DYNAMIC SQL

  • 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

  • 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