x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

But MOM! Do I have to use sp_executesql

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

 

 

Print | posted on Thursday, January 27, 2005 10:13 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: But MOM! Do I have to use sp_executesql

If only you had posted that a day earlier! I swear, I wrote something yesterday that did and exec on dynamic sql and stored the results in a temp table.

Still, thanks!
1/27/2005 12:55 PM | Justin Pitts
Gravatar

# Well it was

If you look at the thread..it was posted in the forum on that day.

Anyway, I'm glad you found it useful

1/27/2005 1:51 PM | Brett
Gravatar

# re: But MOM! Do I have to use sp_executesql

Hi!

I got one problem with this, I have several UDFs, they use each other output to do their work...
now, UDF cannot call stored procedures, so I tried using sp_executesql, as it is an extended procedure not a regular one; this is supposedly supported, however I cant get it to work! it keeps comming back telling me it has to be an extended procedure...
am I missing something?
2/22/2006 11:42 PM | Roberto Peña
Gravatar

# re: But MOM! Do I have to use sp_executesql

But ROBERTO PENA! Do you have to use UDF's?

I find their usage over used.

Just used stored procedures.

Ciao

2/23/2006 6:57 PM | Brett
Gravatar

# re: But MOM! Do I have to use sp_executesql

I found it useful.Thnx really
7/13/2006 5:09 AM | kiran
Gravatar

# re: But MOM! Do I have to use sp_executesql

old news
8/1/2006 1:52 PM | greg
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET