How to get Output parameter value from Dynamic execution of a Query

Discussion in 'Databases' started by shivakhanal, Jul 12, 2008.

0
  1. #1
    Sometimes you have to execute a Query dynamically using Exec(@Sql). This method works fine as long as you don't want any output values from the @Sql query. But there's another method (sp_Executesql) that allows you to execute queries dynamically as well as get their output values.
    The syntax :-

    EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

    Example :-

    DECLARE @IntVariable INT
    DECLARE @SQLString nVARCHAR(500)
    DECLARE @ParmDefinition nVARCHAR(500)
    DECLARE @Lastlname nVARCHAR(30)

    SET @SQLString = 'SELECT @LastlnameOUT = max(lname) FROM
    pubs.dbo.employee WHERE job_lvl = @level'

    SET @ParmDefinition = '@level tinyint, @LastlnameOUT
    varchar(30) OUTPUT'
    SET @IntVariable = 35
    EXECUTE sp_executesql @SQLString, @ParmDefinition, @level
    = @IntVariable, @LastlnameOUT=@Lastlname OUTPUT
    SELECT @Lastlname


    Note: sp_Executesql is a Extended Stored Procedure.
     
    shivakhanal, Jul 12, 2008 IP