Post reply

sp_executesql and result set

  • 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

  • April 16, 2007 at 7:19 am

    #700674

    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.

  • April 16, 2007 at 7:26 am

    #700677

    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

  • April 16, 2007 at 8:43 am

    #700699

    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
     

  • April 16, 2007 at 8:58 am

    #700707

    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

  • April 16, 2007 at 9:20 am

    #700715

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

     

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

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