error in stored procedure

Question

Post reply

error in stored procedure

ssurekha2000

SSCommitted

Points: 1860
More actions
December 5, 2012 at 7:31 pm

#267794

ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int
,@pidname varchar(10)
AS
DECLARE @SQL varchar(500)
SET @SQL = 'delete from ' + @tablename + ' where '+ @pidname + ' = '+ @pid
Conversion failed when converting the nvarchar value 'delete from m_customer where cid = ' to data type int.
i get the above error
if i convert pid int to varchar i dont get error but the recod is not deleted
whts the best way toprevent sql injection and have database secured

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WolfgangE SSCertifiable Points: 6328 More actions · Answer 1

To make your statement work you have to convert @pid in the string concatenation.

SET @SQL = 'delete from ' + @tablename + ' where '+ @pidname + ' = '+ cast( @pid as varchar(50) )

To avoid sql injection you should not use dynamic statements like this at all. Write a separate delete procedure for every table where you want to delete data from.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

first ensure that the value you are getting in @pid is int or varchar. If column data type is varchar then use CAST for it.

Hope, It will works for you 😛

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

ALTER PROCEDURE [dbo].[usp_delete]

@tablename sysname,

@pid int,

@pidname varchar(10)

AS

DECLARE @SQL NVARCHAR(MAX),

@params NVARCHAR(MAX);

SELECT@SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;',

@params = '@pid INT';

EXEC sp_executesql @SQL, @params, @pid = @pid;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Sean Pearce (12/11/2012)
ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int, @pidname varchar(10) AS DECLARE @SQL NVARCHAR(MAX), @params NVARCHAR(MAX); SELECT@SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;', @params = '@pid INT';
EXEC sp_executesql @SQL, @params, @pid = @pid;

Don't forget to test your sp by executing:

EXEC dbo].[usp_delete]

@tablename = 'sometable',

@pid int = 1,

@pidname = '1 = 1 OR 1 '

😎

It's a good idea to always protect your dynamic sql from injection

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I strongly recommend you do not go this approach of generic delete procedures.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

Eugene Elutin (12/11/2012)
Sean Pearce (12/11/2012)
ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int, @pidname varchar(10) AS DECLARE @SQL NVARCHAR(MAX), @params NVARCHAR(MAX); SELECT@SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;', @params = '@pid INT';
EXEC sp_executesql @SQL, @params, @pid = @pid;
Don't forget to test your sp by executing:
EXEC dbo].[usp_delete]
@tablename = 'sometable',
@pid int = 1,
@pidname = '1 = 1 OR 1 '
😎
It's a good idea to always protect your dynamic sql from injection

You can offer some protection by wrapping the execution statement.