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

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myCounts99(mySPID int, myCount int)
GO

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'
SELECT @sql
EXEC(@sql)

DECLARE @x int
SELECT @x = myCount FROM myCounts99 WHERE mySPID = @@SPID
SELECT @x
GO

SET NOCOUNT OFF
DROP TABLE myCounts99
GO

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
execute sp_executesql
          N'select @Count = COUNT(*) from Northwind.dbo.Orders',
          N'@Count int OUT', @Count OUT
SELECT @Count

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....

MOO

EDIT: Here's a dynamic twist from Pat

http://www.dbforums.com/t1171619.html

EDIT:  Here's a good article discussiong the pro's and cons' of dynamic sql

http://www.sommarskog.se/dynamic_sql.html