Monday, March 19, 2012

Dynamically selecting a row from a table

I have a function which i want to return where an id in a table exists somethign like
if exists (select id from @.tablename where id = @.id)
You cannot use dynamic SQL within TSQL UDFs. You should also use dynamic SQL with care. It has security and performance implications if used improperly. Why would you want to write a UDF that does if exists() check on any table? Isn't it easy just to write the query wherever you need it because that will be optimized better. You could consider writing this as a SP instead. And for the dynamic SQL to work you need to grant SELECT permissions for all users on the tables that you would check.

No comments:

Post a Comment