0

I have created a trigger and inside it, there's dynamic SQL gets executed.

Here is sample script:

When I specifying @Text to some value,it work (after exec showing 1 row affetected)but when I am specifying null, record not get added into table(Message:query executed sucessfully).I want like like his,if record not get added into sample table,pls add into Sample 2 without dynamic insertion.How to find out dynamic exec successfully or not.I don't want fetch last identity of table.How to do that?

declare @Inserttbl nvarchar(4000)
declare @TableName varchar(50)
declare @PId int
declare @Text varchar(50)
declare @count int
declare @Status int
set @TableName ='sample'
set @Text = null
set @PId  = 3
set @Status =1

set @Inserttbl = ' Insert into Test.dbo.' +@TableName +' values ('+@PId+','+@Text+','+@Status+')'
exec sp_executesql @Inserttbl
N00b Pr0grammer
  • 4,503
  • 5
  • 32
  • 46
Jui Test
  • 2,399
  • 14
  • 49
  • 76
  • when you build a string, and add null in as a string, what happens? – TZHX Dec 12 '16 at 10:41
  • Question aside I would suggest you use sql parameters to prevent sql injection with this code. – John Dec 12 '16 at 10:45
  • Possible duplicate of [SQL Server String Concatenation with Null](http://stackoverflow.com/questions/2916791/sql-server-string-concatenation-with-null) – TZHX Dec 12 '16 at 10:45
  • @john,I modified my question.pls go through it and let me know,if there is any sql injection attack. – Jui Test Dec 12 '16 at 10:53
  • You need to pass in the parameters to sp_executesql rather that creating a string with the data in as you can abuse the table parameter to drop the database with the current approach. There are issues with having values as paramo in the way though. You may want to ask another question on stackoverflow or learn more about sql injection – John Dec 12 '16 at 17:15

1 Answers1

0

You need to make use of COALESCE when constructing your SQL statement string. I suppose you could use @ROWCOUNT to see if the insert worked providing the statement is formed correctly.

AntDC
  • 1,807
  • 14
  • 23