Skip to main content
Database Administrators

Return to Answer

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link
Community Bot
Community Bot
  • 1

You can't use dynamic SQL inside a function (well, aside from this awful hackthis awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing it).

It's unclear to me why you're doing this as a multi-statement TVF in the first place. You're returning one column and one row. Why not use a stored procedure with an output parameter?

You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing it).

It's unclear to me why you're doing this as a multi-statement TVF in the first place. You're returning one column and one row. Why not use a stored procedure with an output parameter?

You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing it).

It's unclear to me why you're doing this as a multi-statement TVF in the first place. You're returning one column and one row. Why not use a stored procedure with an output parameter?

Source Link
Aaron Bertrand
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing it).

It's unclear to me why you're doing this as a multi-statement TVF in the first place. You're returning one column and one row. Why not use a stored procedure with an output parameter?