Returning values from dynamic SQL by using sp_executesql with an output variable (Or) Error in appending the variable data at the end name dynamically

Question

I just face a problem while working on a stored procedure,

My situation is as below,

I'm calling a stored procedure inside another stored procedure like for example,

EXEC    [SP_ADMIN_INSERT_ITEM_STOCK_DETAILS]
                        @stk_tran_no =  @cash_purchase_no,
                        @stk_tran_date = GetDate(),
                        @tran_type = 'Cash Purchase',
                        @item_code =  @item_code,
                        @quantity = @quantity

Currently in the above code we are passing current date to the parameter @stk_tran_date.

But now I need to pass date to @stk_tran_date by fetching that from some other table like,

select  @stk_tran_date  = Convert(datetime,cash_purchase_date,103) from Cash_Purchase_14  where cash_purchase_no = 'GOH-9/2014'

If you observe my table name is like Cash_Purchase_14 where 14 is a dynamic value which changes every year, as this is 2014 financial year so it looks like Cash_Purchase_14, next year it will be Cash_Purchase_15.

Because of this i use to write these quires first as string then I'll execute them as shown below,

  declare @SQL nvarchar(4000)
  set @SQL =N' Declare @cash_purchase_date1 datetime 
  set @cash_purchase_date1 = (select  cash_purchase_date from Cash_Purchase_'+ @Financialyearpart +'  where cash_purchase_no = ''' + @cash_purchase_no + ''')
                            print @cash_purchase_date1'

  exec executesql @SQL

But I need the value of the variable @cash_purchase_date1 outside this block like below,

EXEC    [SP_ADMIN_INSERT_ITEM_STOCK_DETAILS]
                        @stk_tran_no =  @cash_purchase_no,
                        @stk_tran_date = @cash_purchase_date1,
                        @tran_type = 'Cash Purchase',
                        @item_code =  @item_code,
                        @quantity = @quantity

but it is giving an error like, "declare the variable @cash_purchase_date1"

In Other case i tried like calling the stored procedure in side the string like,

SET @SQL =' Declare @cash_purchase_date1 datetime
                        set @cash_purchase_date1 = (select  cash_purchase_date from Cash_Purchase_'+ @Financialyearpart +'  where cash_purchase_no = ' + @qt + @cash_purchase_no + @qt +')
                        print @cash_purchase_date1
                        EXEC    [SP_ADMIN_INSERT_ITEM_STOCK_DETAILS]
                        @stk_tran_no = ' + @qt + @cash_purchase_no + @qt +',
                        @stk_tran_date = @cash_purchase_date1,
                        @tran_type = ''Cash Purchase'',
                        @item_code = ' + @qt + @item_code + @qt +',
                        @quantity = ' + @quantity

exec executesql @SQL

In this scenario the value of @cash_purchase_date1 is not replacing it simply retains the same.

Please help to get the value of the variable outside the block.

Or

How can I append the value 14 at the end of the table name dynamically using a variable.

I Tried like

Declare @cash_purchase_date1 datetime
set @cash_purchase_date1 =  cash_purchase_date from Cash_Purchase_+ @Financialyearpart

I think i made the problem bit complicated while explaining. Please help me in solving the issue.

Thanks in advance.

Dadapeer Pvg · Accepted Answer · 2014-11-17 08:15:39Z

1

You can return values from dynamic sql by using sp_executesql with an output variable:

 declare @SQL nvarchar(4000);
 declare @cash_purchase_date datetime;

    set @SQL = N'select @cash_purchase_date = cash_purchase_date from Cash_Purchase_' + @Financialyearpart + ' where cash_purchase_no = ''' + @cash_purchase_no + '''';

    exec sp_executesql @SQL, N'@cash_purchase_date datetime OUTPUT', @cash_purchase_date = @cash_purchase_date OUTPUT;

I think this will solve your problem.

edited Nov 17, 2014 at 8:15

Dadapeer Pvg

1672 silver badges14 bronze badges

answered Nov 14, 2014 at 12:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Returning values from dynamic SQL by using sp_executesql with an output variable (Or) Error in appending the variable data at the end name dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related