Need help to debug T-SQL stored procedure that has sql scripts stored in a table

Question

In this stored procedure, I read from a third party vendor table named M_SopInsert.

SQLScript is the column name and every record in this table contains a SQL query that does an UPDATE, INSERT or DELETE.

I can see the actual script when I debug it using Select (commented below). But the script itself doesn't execute and I don't see any errors.

I tried hardcoding an UPDATE statement below and it works fine.

What could be the issue here?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @sopScript nvarchar(1000)

select SQLScript into #ControlTbl from  M_SopInsert 
where soptype = @I_vSOPTYPE and sopnumbe = @I_vSOPNUMBE and lnitmseq = @I_vLNITMSEQ

while exists (select * from #ControlTbl)
begin

   select top 1 @sopScript = SQLScript
   from #ControlTbl

   --exec executesql @sopScript = SQLScript 
   --select @sopScript
   --EXEC sp_executesql @sopScript;
   --EXEC sp_executesql "update SOPQty set QTYORDER = '17.89' where LNIT = '16'"
exec sp_executesql @sopScript = SQLScript
   delete from #ControlTbl where SQLScript = @sopScript

end
drop table #ControlTbl
return (@O_iErrorState)

The syntax error you said you've corrected to make it work should have yielded `Msg 2812, Could not find stored procedure 'SQLScript'.` - is this all of the code, or is this wrapped in `TRY/CATCH` or something else? — Aaron Bertrand, Aug 26 '13 at 23:49

score 3 · Answer 1 · answered Aug 26 '13 at 20:08

3

You don't see any errors because you have an empty catch. This is like setting your alarm clock and then unplugging it. The error you'll probably get is that you can't pass a VARCHAR string to sp_executesql - it needs to be NVARCHAR. Try:

declare @sopScript Nvarchar(1000)
-------------------^

answered Aug 26 '13 at 20:08

Aaron Bertrand

272,866
37
466
490

I had actually removed the catch block but while putting my post, I didn't remove it. So there is no catch block at the moment. Also changing to nvarchar made no changes. For some reason, when I had varchar, the below line was not working. exec executesql @sopScript = SQLScript – Anirudh Aug 26 '13 at 20:23
@Anirudh come on. Debugging 101. Did you try just selecting from #ControlTbl instead of looping and executing the contents? It could also be that there were no rows inserted into the #temp table. It could also be that you are executing it one database and checking if the commands were executed on another. Does your connection string have `AttachDbFileName` in it? – Aaron Bertrand Aug 26 '13 at 20:36
I replaced "exec sp_executesql @sopScript = SQLScript" with "exec sp_executesql @sopScript" and it worked.. I don't know what difference each has. – Anirudh Aug 26 '13 at 22:50

score 0 · Accepted Answer · answered Aug 27 '13 at 14:55

0

I replaced "exec sp_executesql @sopScript = SQLScript" with "exec sp_executesql @sopScript" and it worked.. I don't know what difference it would make.

answered Aug 27 '13 at 14:55

Anirudh

581
5
14
32

Need help to debug T-SQL stored procedure that has sql scripts stored in a table

2 Answers2