InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:43:25 GMT20http://www.sqlservercentral.com/Forums/Topic1393292-392-1.aspx[quote][b]Eugene Elutin (12/11/2012)[/b][hr][quote][b]Sean Pearce (12/11/2012)[/b][hr][code="sql"]ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int, @pidname varchar(10)ASDECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX);SELECT @SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;', @Params = '@pid INT';EXEC sp_executesql @SQL, @Params, @pid = @pid;[/code][/quote]Don't forget to test your sp by executing:[code="sql"]EXEC dbo].[usp_delete] @tablename = 'sometable', @pid int = 1, @pidname = '1 = 1 OR 1 '[/code]:cool:It's a good idea to always protect your dynamic sql from injection[/quote]You can offer some protection by wrapping the execution statement.[code="sql"]ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int, @pidname varchar(10)ASDECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX);SELECT @SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;', @Params = '@pid INT';IF EXISTS(SELECT * FROM sys.columns WHERE name = @pidname AND OBJECT_NAME(object_id) = @tablename)BEGIN EXEC sp_executesql @SQL, @Params, @pid = @pid;END;[/code]Tue, 11 Dec 2012 07:15:15 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1393292-392-1.aspxI strongly recommend you do not go this approach of generic delete procedures.Tue, 11 Dec 2012 03:36:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1393292-392-1.aspx[quote][b]Sean Pearce (12/11/2012)[/b][hr][code="sql"]ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int, @pidname varchar(10)ASDECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX);SELECT @SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;', @Params = '@pid INT';EXEC sp_executesql @SQL, @Params, @pid = @pid;[/code][/quote]Don't forget to test your sp by executing:[code="sql"]EXEC dbo].[usp_delete] @tablename = 'sometable', @pid int = 1, @pidname = '1 = 1 OR 1 '[/code]:cool:It's a good idea to always protect your dynamic sql from injectionTue, 11 Dec 2012 03:32:26 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1393292-392-1.aspx[code="sql"]ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int, @pidname varchar(10)ASDECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX);SELECT @SQL = 'DELETE FROM [' + @tablename + '] WHERE ' + @pidname + ' = @pid;', @Params = '@pid INT';EXEC sp_executesql @SQL, @Params, @pid = @pid;[/code]Tue, 11 Dec 2012 01:15:12 GMTSean Pearcehttp://www.sqlservercentral.com/Forums/Topic1393292-392-1.aspxfirst 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 :-PThu, 06 Dec 2012 03:39:56 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1393292-392-1.aspxTo 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.Thu, 06 Dec 2012 00:02:07 GMTWolfgangEhttp://www.sqlservercentral.com/Forums/Topic1393292-392-1.aspxALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int ,@pidname varchar(10)ASDECLARE @SQL varchar(500)SET @SQL = 'delete from ' + @tablename + ' where '+ @pidname + ' = '+ @pid [b]Conversion failed when converting the nvarchar value 'delete from m_customer where cid = ' to data type in[/b]t.i get the above errorif i convert pid int to varchar i dont get error but the recod is not deletedwhts the best way toprevent sql injection and have database securedWed, 05 Dec 2012 19:31:58 GMTssurekha2000