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

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

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
execute sp_executesql
          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




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



# 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

# 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

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


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

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


2/23/2006 6:57 PM | Brett

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

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

# 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