OK, it seems often asked how to return values from dynamic sql. My initial reastion was to do something like the following. Which works, is flexible, is highly dynamic, and does take a bit of setup
SET NOCOUNT ON
CREATE TABLE myCounts99(mySPID int, myCount int)
DECLARE @sql varchar(8000)
SELECT @sql = 'DELETE FROM myCounts99 WHERE mySPID = ' + CONVERT(varchar(4),@@SPID) + CHAR(13)
+ 'INSERT INTO myCounts99(mySPID, myCount) SELECT ' + CONVERT(varchar(4),@@SPID) + ', COUNT(*) FROM Orders'
DECLARE @x int
SELECT @x = myCount FROM myCounts99 WHERE mySPID = @@SPID
SET NOCOUNT OFF
DROP TABLE myCounts99
But as Nigel http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578 points out in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45193 it can be much simpler
DECLARE @Count int
N'select @Count = COUNT(*) from Northwind.dbo.Orders',
N'@Count int OUT', @Count OUT
And, in reading Books Online (BOL) the benefits of sp_executesql are that it may reuse a plan that is stored for the sql. Where as EXEC has to be recompiled each and every time. Now being lazy, EXEC is nice and simple, no thought to syntax is needed for the most part, and I'm sure you can go real nuts with EXEC and dynamic. BUT, if you need to return a value, it sure seems like the way to go.
Also, now that I've bloged this example, I don't have to remeber how to use it anymore. Like in the thread, I got hung up on the OUT paramater...notice it's not OUTPUT...ah M$ standards....
EDIT: Here's a dynamic twist from Pat
EDIT: Here's a good article discussiong the pro's and cons' of dynamic sql