Post reply

stored procedure with OUTPUT parameter in dynamic sql problem

  • October 15, 2014 at 12:49 pm

    #291021

    Note: @procName,@strAccount,@intHospital,@patType are passed to this procedure from another procedure

    Note: The @procname procedure also takes the above parameters and @outDupCheck as output parameter

    DECLARE @sqlStr NVARCHAR(500)

    DECLARE @ParmDefinition NVARCHAR(500)

    DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1)

    DECLARE @parmRET1 int

    SET @parmINAccount = @strAccount

    SET @parmINHospId = @intHospital

    SET @ParmINpatType = @patType

    SET @sqlStr = N'Exec ' + @procName + ' @strAccount,@intHospital,@patType, @outDupCheck OUTPUT'

    SET @ParmDefinition=N'@strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';

    EXECUTE sp_executesql @sqlStr, @ParmDefinition, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1

    SELECT @parmRET1

    --The parameter @parmRET1 returns NULL instead of 1 .

    The @procName returns value 1 correctly if I run it separately ( outside of the dynamic sql)

    Not sure what is wrong here...

  • October 15, 2014 at 1:04 pm

    #1752665

    You need to tell sp_executesql that @outDupCheck is an output parameter as well. So your query needs to look that this:

    EXECUTE sp_executesql @sqlStr, @ParmDefinition, @strAccount = @parmINAccount, @intHospital = @parmINHospId, @patType = @ParmINpatType, @outDupCheck = @parmRET1 OUTPUT

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • October 15, 2014 at 1:07 pm

    #1752666

    Argggh!! Finally !!! Thanks a bunch.:-)

Viewing 3 posts - 1 through 2 (of 2 total)

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