This stored procedure accepts tablename, fieldname, fieldvalue and
fieldtype 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), @fieldname
varchar(50)=NULL,
@fieldvalue varchar(2000)=NULL , @fieldtype _
int=NULL with recompile
as
declare @position int
declare @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)
end
else
begin
set @newvalue=@fieldvalue
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)
end
return
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'
