none
sp_executesql + Output parameters

    Question

  • can somebody let me understand how this Output parameters works in sp_executesql / Dynamic TSQL 

     

    I want to store a integer value resulting from executing a dynamic query. I was trying to follow the syntax but to be honest I didn't get it, and resulted in err. So can somebody help me in understanding how it works and how and where to declare the variables to be output and what command does that Output etc.

     

    Thanks a lot in advance

     

     

    Tuesday, September 04, 2007 10:56 AM

Answers

  • If you want to use the parameterized dynamic sql statement - using sp_executesql, remember the following key notes,

    1.     The Dynamic SQL should be NVarchar => First parameter

    2.     The variables used in the Dynamic SQL should be declared, output parameter should be noted as OUTPUT/OUT after the declaration => Second parameter

    3.     The values of the variable (all declared on second parameter) need to be passed, NOTE: if the parameter is declared as OUTPUT in declaration(second parameter), the OUTPUT/OUT notation should be used when pass the value of the parameter & The OUTPUT parameter should be variable.

    Sample,

    Code Snippet

    Declare @dynmaicsql as  nvarchar(1000);

    Declare @dynamicparamdec as nvarchar(1000);

     

    Declare @valueofid as int

    Declare @returnedname as varchar(100)

     

     

    Set @dynmaicsql = 'Select @name=Name from Sysobjects Where id=@id'

    Set @dynamicparamdec = '@name varchar(100) output, @id int'

     

    Set @valueofid = object_id('Syscolumns')

     

    execute sp_executesql

                                         @dynmaicsql   

                                        ,@dynamicparamdec

                                        ,@returnedname  output

                                        ,@valueofid

     

    Select @returnedname

     

     

     

    Tuesday, September 04, 2007 5:55 PM

All replies

  • I agree that the Books Online examples could be a touch clearer. Hopefully my example below will be a bit clearer:

     

    Code Snippet

    USE TempDB
    GO

    CREATE TABLE YourTable
    (YourID INT,
    FirstName VARCHAR(30))

     

    INSERT INTO YourTable
    VALUES (1, 'Richard')

     

    DECLARE @Parms nvarchar(500);
    DECLARE @FirstName varchar(30);

     

    EXECUTE sp_executesql

    N'SELECT @FirstNameInternal = FirstName FROM YourTable WHERE YourID = @int1', --this is your parameterized sql statement
     N'@int1 INT, @FirstNameInternal VARCHAR(30) OUTPUT', --these are your declared parameters used in the scope of the sql statement
     @int1 = 1, -- this is setting the value of an internal input parameter
     @FirstNameInternal = @FirstName OUTPUT --this is the setting of an internal output parameter to your declared variable

     

    SELECT @FirstName

     

    The trick is to remember that the parameters you declare in the @Params variable are not declared outside sp_executesql. They are placeholders for the values you set them to in the last parameters. You are essentially forcing SQL Server to parameterize the query:

     

    Read up on this in Books Online- this page is a good starting point:

     

    http://msdn2.microsoft.com/en-us/library/ms186219.aspx

     

    Hope that makes sense and good luck!

    Tuesday, September 04, 2007 11:16 AM
  • If you want to use the parameterized dynamic sql statement - using sp_executesql, remember the following key notes,

    1.     The Dynamic SQL should be NVarchar => First parameter

    2.     The variables used in the Dynamic SQL should be declared, output parameter should be noted as OUTPUT/OUT after the declaration => Second parameter

    3.     The values of the variable (all declared on second parameter) need to be passed, NOTE: if the parameter is declared as OUTPUT in declaration(second parameter), the OUTPUT/OUT notation should be used when pass the value of the parameter & The OUTPUT parameter should be variable.

    Sample,

    Code Snippet

    Declare @dynmaicsql as  nvarchar(1000);

    Declare @dynamicparamdec as nvarchar(1000);

     

    Declare @valueofid as int

    Declare @returnedname as varchar(100)

     

     

    Set @dynmaicsql = 'Select @name=Name from Sysobjects Where id=@id'

    Set @dynamicparamdec = '@name varchar(100) output, @id int'

     

    Set @valueofid = object_id('Syscolumns')

     

    execute sp_executesql

                                         @dynmaicsql   

                                        ,@dynamicparamdec

                                        ,@returnedname  output

                                        ,@valueofid

     

    Select @returnedname

     

     

     

    Tuesday, September 04, 2007 5:55 PM
  •  

    Excellent Discription. Thanks a lot Mani
    Tuesday, September 04, 2007 6:00 PM