sp_executesql and result set

Question

Post reply

sp_executesql and result set

Carl B.

SSCertifiable

Points: 7931
More actions
April 16, 2007 at 6:55 am

#175857

Hello Everyone,
Can we have output parameter using sp_executesql?
I want to managed values (in avriables) that are returned from the execution of a query with sp_executesql. Is it possible.
E.g:
sp_executesql 'SELECT @count = count(*) FROM mytable'
Is it possible to put the result fomr the count(*) into a variable?
Regards.
Carl

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65144 More actions · Answer 1

Yes, lookup sp_executesql in BOL

Your example would like like this

DECLARE @count int

EXEC sp_executesql N'SELECT @count = count(*) FROM mytable', N'@count int output', @count OUTPUT

SELECT @count

Although you do not need to use dynamic SQL for your example but then I presume it is just and example

Far away is close at hand in the images of elsewhere.
Anon.

Carl B. SSCertifiable Points: 7931 More actions · Answer 2

Thanks David,

The only part that was missing is the "@count OUTPUT" at the end.

Yes I need to do dynamic SQL because in my particular environment, this script can be run on a database that does not contains the mytable table. So I will use something like:

IF EXISTS (SELECT 'X' FROM sysobjects WHERE name = 'mytable' AND Type = 'U' AND uid= user_id())

BEGIN

... -- PUt all the dynamic code here.

END

Regards.

Carl

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

You don't need it for the code you showed. This runs just fine for me:

declare @count int

if exists (select 'x' from sysobjects where name = 'mytable' and type = 'u' )
 begin
 select @count = count(*) from mytable
 end

Carl B. SSCertifiable Points: 7931 More actions · Answer 4

Thanks Michael,

You seem to be rigth. Its different from Oracle that compile the entire script not only the code that is executed in fact.

Its strange because we've tested it last week and it was not working... Maybe something we miised something. I'll check it all.

Best regards,

Carl

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

SQL Server uses last binding, meaning that tables a stored procedure or script references do not have to exist when it is compiled.