This stored procedure accepts tablename, fieldname, fieldvalue andfieldtype as parameters. If no fieldname, fieldvalue is passed, it deletes all records from the table passed. Fieldvalue could be a single value or list of values delimited by a comma. If fieldtype is NULL or 1 it will consider the datatype of the field as char, varchar. Else it is considered as a numeric value.
create procedure deleterecord @tablename _varchar(50), @fieldnamevarchar(50)=NULL,@fieldvalue varchar(2000)=NULL , @fieldtype _int=NULL with recompileasdeclare @position intdeclare @newvalue varchar(2000)declare @sqlstat varchar(500)if @fieldname is NULL begin -- no fieldname passed...so delete all the _records set @sqlstat = ' delete from ' + @tablename exec(@sqlstat) endelsebegin set @[email protected] set @position=charindex(',', @fieldvalue) while @position >0 begin set @sqlstat = " delete from " + @tablename _+ " where " + @fieldname +" = " if @fieldtype is NULL or @fieldtype = 1 begin set @sqlstat = @sqlstat + "'" end set @sqlstat = @sqlstat + left(@newvalue, _charindex(',',@newvalue)-1) if @fieldtype is NULL or @fieldtype = 1 begin set @sqlstat = @sqlstat + "'" end set @newvalue=right(@newvalue,len(@newvalue) -charindex(',',@newvalue)) set @position=charindex(',', @newvalue) -- print @sqlstat exec (@sqlstat) end set @sqlstat = " delete from " + @tablename + _" where " + @fieldname + "= " if @fieldtype is NULL or @fieldtype = 1 begin set @sqlstat = @sqlstat + "'" end set @sqlstat = @sqlstat + @newvalue if @fieldtype is NULL or @fieldtype = 1 begin set @sqlstat = @sqlstat + "'" end print @sqlstat exec(@sqlstat)endreturn
Example:
exec deleterecord 'country' ,'countryname' , 'abcd'exec deleterecord 'country' ,'countryid' , '900'exec deleterecord 'country' ,'countryname' , _'abcd,efg,hij,klm'exec deleterecord 'country' ,'countryid' , _'900,901,902,903'exec deleterecord 'country'