1.When you use stored procedures with only static SQL, users do not need direct permissions to access the tables, only permissions to execute the stored procedures, and thus you can use the stored procedure to control what users may and may not do.
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
