Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
Nov 30, 2001

A Universal Delete Stored Procedure that Deletes Records from any Table


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'
Sanjay Nair
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date