CODEWITHSUNDEEP
sqlt-sql
Bill Mannion
Bill Mannion

Reputation: 47

Scope issue with stored procedure string parameter being returned from dynamic SQL

I have a stored procedure for creating inventory transactions that requires the assembly of a description. Since other inventory stored procedures will also need to assemble their descriptions similarly, I am trying to create a helper stored procedure.

This helper will use standard parameters and construct the description. The trouble I am having is returning the string Description back to the inventory transaction.

A Inventory transaction calls the helper this way:

declare @TransDescription nvarchar(256) 
declare @TransDescOut nvarchar(256)

EXEC [dbo].[sp_KF_Helpers_CreateInvTransDescription] 
     @TransactionTypeID, @UserName, @OwnerTypeID, @OwnerID, 
     @TransDesc = @TransDescOut OUTPUT

SET @TransDescription = @TransDescOut

Then I use @TransDescription as a value for inserting into column data.

The helper code is:

    CREATE PROCEDURE [dbo].[sp_KF_Helpers_CreateInvTransDescription]
    (   @TransactionTypeID int,
        @UserName nvarchar(256),
        @OwnerTypeID int,
        @OwnerID int,
        @TransDesc varchar(256) OUTPUT
    )
    AS
    BEGIN
    SET NOCOUNT ON;

declare @rslt int = 0
declare @strTyepID varchar(256) = @TransactionTypeID
declare @strOwnerID varchar(256) = @OwnerID
declare @intOwnerTypeID int = @OwnerTypeID
declare @OwnerStr varchar(256) = 'KF_'
declare @OwnerIDStr varchar(256) = (select Description from KF_OwnerType where ID = @intOwnerTypeID)

select @OwnerStr = @OwnerStr + @OwnerIDStr
declare @sql1 nvarchar(4000)

Select @sql1 = 'Select Top 1 (a.Description + '' - '' + ' + @OwnerStr + '.Name) TransDesc 
from KF_InventoryTransactionType a, KF_OwnerType c, ' + @OwnerStr + ' 
where a.ID = ' + @strTyepID + ' and ' 
+ @OwnerStr + '.ID = ' + @strOwnerID 

exec SP_EXECUTESQL @sql1, N'@TransDesc varchar output ', @TransDesc output
 End

As you can see, I am using dynamic SQL to generate the description. The problem is that the help code generates the correct description, but does not pass it back as output.

Anyone know why or where I am losing scope for returning my output description?

Upvotes: 2

Views: 1169

Answers (1)

Andomar
Andomar

Reputation: 238126

You forgot to assign the variable. Try:

select top 1 @TransDesc = a.Description + '' - '' + ' + @OwnerStr + '.Name
...

Also, change the part where you are declaring the parameter of the dynamic script (@TransDesc), or you will run into another issue. Currently the parameter is being declared like this:

@TransDesc varchar output

which is equivalent to

@TransDesc varchar(1) output

Most likely, it should be

@TransDesc varchar(256) output

instead.

Upvotes: 4

Related Questions