Post reply

ALTER TABLE IN SP!

  • August 11, 2004 at 5:19 am

    #161306

    Hello!

    I tried to create this Stored Proc but I receive error on line 4:

    Error 170: Line 4: Incorrect syntax near @TablName'

    CREATE PROCEDURE DisableTrigger

    @TablName varchar(255)

     AS

    ALTER TABLE @TablName

      DISABLE TRIGGER tr_i_AUDIT_@TablName

    GO


    -Lars

    Please only reply to this newsgroup. All mails would be bounced back.

  • August 11, 2004 at 5:37 am

    #518451

    Hi Lars,

    Thats because the table name has to be actual name and not variable.  The way around this is to use dynamic sql.  There is a performance hit, not much, but you have to be aware of it.

    Try this instead

    CREATE PROCEDURE DisableTrigger @TablName VARCHAR(255)

    AS

    DECLARE @sql VARCHAR(300)

    SELECT @sql = "ALTER TABLE " + @TablName + " DISABLE TRIGGER tr_i_AUDIT_" + @TablName

    EXECUTE(@sql)

    go

    Regards

    Richard...

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply