But this is not true for SP which uses dynamic sql. If you have the code bellow in your SP:
EXEC ('select * from Product'), then the user must have the select right to the table Product.
2.CLR code can be a valuable supplement for tasks that are difficult to perform in T-SQL, but you yet want to perform server-side.
For example, you may want to browse the File System of the Sql Server Machine, and the browsing result will be used in a SP.Then you can package the function of File-System-Browsing into a sql/clr function, and call it in your SP like any common sql functions.
3.the dynamic SQL is not part of the stored procedure, but constitutes its own scope. Invoking a block of dynamic SQL is akin to call a nameless stored procedure created ad-hoc. And yes, the block of dynamic sql even has it's own query plan!
4.Sometimes you may want to get the result of a dynamic sql, like what I did today:
/*check whether the db is exist, if so, just drop it.*/
DECLARE @i INT
SET @sqlCmd = N'SELECT @i = COUNT(*) FROM master.sys.databases where name=' + @dbname
EXEC sp_executesql
@query = @sqlCmd,
@params = N'@i INT OUTPUT',
@i = @i OUTPUT
if(@i>0)
begin
exec('drop database '+@dbName)
select @error = @@error
if(@error<>0)
begin
raiserror('error when try to drop a not exist db!',16,1)
goto ErrorPoint
end
end
DECLARE @i INT
SET @sqlCmd = N'SELECT @i = COUNT(*) FROM master.sys.databases where name=' + @dbname
EXEC sp_executesql
@query = @sqlCmd,
@params = N'@i INT OUTPUT',
@i = @i OUTPUT
if(@i>0)
begin
exec('drop database '+@dbName)
select @error = @@error
if(@error<>0)
begin
raiserror('error when try to drop a not exist db!',16,1)
goto ErrorPoint
end
end