multiple results returned by cursor

• scotsditch

  Ten Centuries

  Points: 1020

  More actions

  October 2, 2018 at 4:33 pm

  #364299

  When I run the code below I get one query result with "error" 0, and another query result with "result" 1,1,1,2,2,2.  I would've expected the error and "result" 1,2.  

  I'm wondering why do I get the 4 other duplicate values?

  Code:

  --testing try catch
IF object_id('tempdb..#TempTestValues') is not null
BEGIN
 DROP TABLE #TempTestValues
END

  create table #TempTestValues (val1 int,val2 int)

  insert into #TempTestValues values(1, 1),(1,0),(4,2)

  --table for results
IF object_id('tempdb..#TempResults') is not null
BEGIN
 DROP TABLE #TempResults
END

  create table #TempResults (result int)

  --template for cursor

  declare @SQLCmdTemplate nvarchar(MAX) = N'

  begin try

  insert into #TempResults
select ''~val1~''/''~val2~'' from #TempTestValues

  end try

  begin catch

  select ''~val2~'' as error

  end catch

  ';

  --add cursor
declare @SQLCmd nvarchar(MAX)

  declare @field1 int
declare @field2 int
declare cur CURSOR LOCAL for
  select val1, val2 from #TempTestValues

  open cur

  fetch next from cur into @field1, @field2

  while @@FETCH_STATUS = 0 BEGIN

    set @SQLCmd = replace(replace(replace(replace(replace(@SQLCmdTemplate,'~val1~',quotename(@field1)),'~val2~',quotename(@field2)),'[',''),']',''),'''','');
print @SQLCmd;
exec sys.sp_executesql @stmt = @SQLCmd;

    fetch next from cur into @field1, @field2
END

  close cur
deallocate cur

  select * from #TempResults

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  October 2, 2018 at 6:25 pm

  #2007813

  scotsditch - Tuesday, October 2, 2018 4:33 PM

  When I run the code below I get one query result with "error" 0, and another query result with "result" 1,1,1,2,2,2.  I would've expected the error and "result" 1,2.  

  I'm wondering why do I get the 4 other duplicate values?

  Code:

  --testing try catch
IF object_id('tempdb..#TempTestValues') is not null
BEGIN
 DROP TABLE #TempTestValues
END

  create table #TempTestValues (val1 int,val2 int)

  insert into #TempTestValues values(1, 1),(1,0),(4,2)

  --table for results
IF object_id('tempdb..#TempResults') is not null
BEGIN
 DROP TABLE #TempResults
END

  create table #TempResults (result int)

  --template for cursor

  declare @SQLCmdTemplate nvarchar(MAX) = N'

  begin try

  insert into #TempResults
select ''~val1~''/''~val2~'' from #TempTestValues

  end try

  begin catch

  select ''~val2~'' as error

  end catch

  ';

  --add cursor
declare @SQLCmd nvarchar(MAX)

  declare @field1 int
declare @field2 int
declare cur CURSOR LOCAL for
  select val1, val2 from #TempTestValues

  open cur

  fetch next from cur into @field1, @field2

  while @@FETCH_STATUS = 0 BEGIN

    set @SQLCmd = replace(replace(replace(replace(replace(@SQLCmdTemplate,'~val1~',quotename(@field1)),'~val2~',quotename(@field2)),'[',''),']',''),'''','');
print @SQLCmd;
exec sys.sp_executesql @stmt = @SQLCmd;

    fetch next from cur into @field1, @field2
END

  close cur
deallocate cur

  select * from #TempResults

  You have three rows in #TempTestValues. On the first insert, you are executing an insert using select 1/1 from #TempTestValues. So you insert the value of 1/1 for every row in #TempTestValues. Then you get an error, there is no insert. Then you execute an insert using select 4/2 from #TempTestValues. So you insert the value of 4/2 for every row in #TempTestValues.
  From what you were expecting, your template would need to be declared with something like:
  declare @SQLCmdTemplate nvarchar(MAX) = N'

  begin try

  insert into #TempResults
select ''~val1~''/''~val2~''

  end try
etc......


  Sue