Functions are the most restrictive code blocks you can define in SQL Server. They perform just like stored procedures in which it can return set and output parameters. But you cannot create or modify objects and the data within the object in a function except the objects are table variables and table value type variables. Actually any transaction depended objects are not allowed to run in a function, such as SEND and RECEIVE. You can run exec proc within a function but only some extended procedures are allowed. One is not allowed but most developers(including me) desire is to run sp_executesql in a function. You can get around it by creating CLR functions.

Continue reading “Run Dynamic SQL within Function” →