Posted by Erland Sommarskog on 09/29/21 11:33

Steve (sjc4914@yahoo.com) writes:
> This looks somewhat promising. I'll give it a try over the weekend and
> let you know how if it works out. I tried to move from a stored
> procedure to a stored function yesterday and ended up frustrated. I
> could almost get it to work, but I couldn't come up with a way to return
> the scalar value from the EXECUTE @sql.

You can use sp_executesql for that:

EXEC sp_executesql N'SELECT @cnt = COUNT(*) FROM tbl', N'@cnt int OUTPUT',
@cnt = @count OUTPUT

However, you cannot use dynamic SQL in user-defined functions.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]