Home » Tip Bank

Language: Enterprise
Expertise: Advanced

Nov 30, 2001

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'