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
– Adish, Commented Nov 3, 2015 at 12:47
You can't use parametrized table names (like your from @tabname1in) in from clause. — Arvo
– Arvo, Commented Nov 3, 2015 at 13:05
You need to build your sqlstr using 'select ....from ' + \@tabname1 +',' + \@tabname2 etc. — Steve Ford
– Steve Ford, Commented Nov 5, 2015 at 12:46

Chris J · Accepted Answer · 2016-01-20 13:55:05Z

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, 2016 at 13:55

Chris J

9361 gold badge8 silver badges27 bronze badges

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

2 Comments

underscore_d Over a year ago

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?

Chris J Over a year ago

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

JosephStyons · Accepted Answer · 2016-01-20 15:30:09Z

0

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

answered Jan 20, 2016 at 15:30

JosephStyons

59.1k64 gold badges169 silver badges240 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related