December 24, 2003 at 4:35 am
Hi
I want to write a strored procedure which will take a set of table names as arguments and a flag value.
If the flag value is DEL it should delete all the tables
If the flag value is UPD it should update the column lastmodifieddate to date().
For example
exec SP_DELORUPDATE('A,B,C','DEL');
This should delete all rows from the tables A,B,C
exec SP_DELORUPDATE('X,Y,X','UPD');
This should update the lastmodifieddate column in the tables X,Y,Z to date();
The procedure should have a dynamic sql to accomplish this task as the table name arguments are known only at the runt ime.
Please help me to write a TSQL for this .
Thanks
December 24, 2003 at 5:01 am
Try this out.
Create Procedure sp_test
@pTableNames varchar(200),
@pAction varchar(3)
as begin
DECLARE @npos Smallint
DECLARE @nStart Smallint
DECLARE @nFName Varchar(50)
Set @npos = Len(@pTableNames)
Set @nStart = 1
WHILE (@nPos > 0)
BEGIN
Set @npos = CHARINDEX(',',@pTableNames,@nStart)
if @npos = 0
Set @nFName = Substring(@pTableNames,@nStart,Len(@pTableNames)-(@nStart-1))
else
Set @nFName = Substring(@pTableNames,@nStart,@nPos-@nStart)
IF Len(Ltrim(@nFName)) > 0
begin
IF @pAction = 'UPD'
Exec('UPDATE '+@nFname+' Set lastmodifieddate = getdate()')
IF @pAction = 'DEL'
Exec('DELETE FROM '+@nFname)
Set @nStart = @npos + 1
END
END
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply