Using OUTPUT Parameters within Dynamic SQL unfortunately I only get the null value

Question

I have took a look at this page of Microsoft.com https://msdn.microsoft.com/en-us/library/ms188001.aspx ,but What I have done wrong?All I only could get the null value form a dynamic sql statement

DECLARE @ParmDefinition NVARCHAR(500)
declare @mynum int,@tempin int
declare @mychar char(5),@tempchar  char(4)
declare @mysqluse char(19)
declare @comname char(200),@sqlstr nvarchar(1000)
declare @tabname1 char(30),@tabname2 char(30)

select @mynum=1
while @mynum<99 
begin

 select @tempin=1000+@mynum
 select @tempchar=convert(char(4),@tempin)

 if @tempchar is null
 begin
  select 'exception occured,program terminated!'
  return
end 

     select @mychar='ms'+right(@tempchar,3)--dynamic change the database

      select @comname=@mychar


     declare @ldt_ksrq datetime, @ldt_jsrq datetime, @ls_prod varchar(50)
     set @ldt_ksrq = '2015-09-26'   
     set @ldt_jsrq = '2015-10-26'  
     set @ls_prod  = '3%'           



 declare @ld_kcje numeric(10,2), @ld_xsje numeric(10,2), @ld_yhje1 numeric(10,2), @ld_thje numeric(10,2), @ld_yhje2 numeric(10,2)
 declare @ld_dcje numeric(10,2), @ld_drje numeric(10,2), @ld_jcje numeric(10,2), @ld_tcje numeric(10,2)
 declare @ld_kcjeout numeric(10,2), @ld_xsjeout numeric(10,2), @ld_yhje1out numeric(10,2), @ld_thjeout numeric(10,2), @ld_yhje2out numeric(10,2)
 declare @ld_dcjeout numeric(10,2), @ld_drjeout numeric(10,2), @ld_jcjeout numeric(10,2), @ld_tcjeout numeric(10,2)
 SET NOCOUNT ON
 select @tabname1=@mychar+'.dbo.prod_dep'
 select @tabname2=@mychar+'.dbo.product'
 select @sqlstr='select @ld_kcjeout = sum(pd.lest_num * p.retail_price + pd.part_num  *  p.part_price ) from @tabnamein1 pd, @tabnamein2 p where pd.prod_no = p.prod_no and pd.prod_no not like @ls_prodin'
  SET @ParmDefinition = N'@ld_kcjeout numeric(10,2) OUTPUT,@tabnamein1 char(30),@tabnamein2 char(30),@ls_prodin varchar(50)'
   exec  sp_executesql @sqlstr,
       @ParmDefinition,
       @ld_kcjeout=@ld_kcje OUTPUT,
       @tabnamein1=@tabname1,
       @tabnamein2=@tabname2, 
       @ls_prodin=@ls_prod


    select @tabname1=@mychar+'.dbo.bill_main'
    select @tabname2=@mychar+'.dbo.bill_sub'
    select @sqlstr='select @ld_xsjeout  = sum((s.num + s.part_num) * s.retail_price),
   @ld_yhje1 = sum((s.num + s.part_num) * (s.yj_price - s.retail_price)) from @tabname1in m, @tabname2in s where m.bill_no = s.bill_no and m.bill_date >= @ldt_ksrqin and m.bill_date < @ldt_jsrqin and m.bill_no like  ''S%'' and s.prod_no not like @ls_prod and (s.num + s.part_num)>0'

    SET @ParmDefinition = N'@ld_xsjeout numeric(10,2) OUTPUT,@ld_yhje1out numeric(10,2) OUTPUT,@tabname1in char(30),@tabname2in char(30),@ls_prodin varchar(50),@ldt_ksrqin datetime,@ldt_jsrqin datetime'

    EXEC sp_executesql  @sqlstr,
    @ParmDefinition,
    @ld_kcjeout=@ld_kcje OUTPUT,
    @ld_yhje1out=@ld_yhje1 OUTPUT,
    @tabname1in=@tabname1,
    @tabname2in=@tabname2,
    @ls_prodin=@ls_prod  ,
    @ldt_ksrqin=@ldt_ksrq,
    @ldt_jsrqin=@ldt_jsrq;


....
end

I just can't figure out why value of variable of @ld_kcje,@ld_yhje1 all are null which the output variable of SQL statment regards Ken

Have you printed the @sqlstr and made sure it is what you expected? — Adish, Nov 03 '15 at 12:47
You can't use parametrized table names (like your `from @tabname1in`) in from clause. — Arvo, Nov 03 '15 at 13:05
http://stackoverflow.com/questions/1325044/dynamic-sql-passing-table-name-as-parameter?rq=1 — Tab Alleman, Nov 03 '15 at 13:55
You need to build your sqlstr using 'select ....from ' + \@tabname1 +',' + \@tabname2 etc. — Steve Ford, Nov 05 '15 at 12:46

score 0 · Answer 1 · answered Jan 20 '16 at 13:55

0

it would be something along these lines when combining the variables, the square brackets to cater for any odd characters in your variables.

            Select  @sqlstr = 'select ['+@ld_kcjeout+'] = sum(pd.lest_num * p.retail_price + pd.part_num  *  p.part_price ) from ['+@tabname1+'] pd, ['+@tabname2+'] p where pd.prod_no = p.prod_no and pd.prod_no not like '''+@ls_prod+'''';

Also, I would suggest

creating a temporary results table at the beginning of your script to capture data
Instead of returning 'exception occured,program terminated!' use the RAISERROR message otherwise your data sets will be off further down your process
Use sp_MSforeachdb for executing SQL against multiple databases

answered Jan 20 '16 at 13:55

Chris J

938
1
8
27

If the table names were invalid, surely the user would get an error, not just a `null` result. And `sp_MSforeachdb` is undocumented and may disappear at any time; do you really want to recommend something like that? – underscore_d Jan 20 '16 at 13:57
Table names invalid? I'm just laying out how to put together variables, in particular when it comes to table and field names. Once the code is executing as it should be, with table names and field names we can look at what is being returned. `sp_MSforeachdb` would work in this instance, you can always write your own [link](https://sqlblog.org/2020/05/12/bad-habits-to-kick-relying-on-undocumented-behavior) – Chris J Jan 20 '16 at 14:08

score 0 · Answer 2 · answered Jan 20 '16 at 15:30

There are two easy ways to do this. Since you know what output you expect, why not just drop the data into a temp table and then select from that (method 1 below).

--method 1: drop dynamic output into a table
declare @result table(value nvarchar(100));
declare @sql nvarchar(max) = 'select 100';
insert into @result
exec sp_executesql @sql
select * from @result

--method 2: capture the output directly
declare @capturedoutput int;
set @sql = 'select @capturedoutput = 100';
EXEC sp_executesql @sql
 ,N'@capturedoutput int OUTPUT'
 ,@capturedoutput=@capturedoutput OUTPUT
select @capturedoutput

Using OUTPUT Parameters within Dynamic SQL unfortunately I only get the null value

2 Answers2