CODEWITHSUNDEEP
t-sql
Justin808
Justin808

Reputation: 21522

Why does this TSQL fail?

IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
BEGIN
    BEGIN TRANSACTION
    GO
        CREATE TABLE dbo.Tmp_Templates
            (
            ID int NOT NULL IDENTITY (1, 1),
            isHidden bit NULL,
            FileName nvarchar(255) NOT NULL,
            Name nvarchar(255) NOT NULL,
            Description nvarchar(1024) NULL,
            UploadedByTVDBUsersID int NOT NULL,
            Created datetime NOT NULL
            )
        GO
        SET IDENTITY_INSERT dbo.Tmp_Templates ON
        GO
        IF EXISTS(SELECT * FROM dbo.Templates)
             EXEC('INSERT INTO dbo.Tmp_Templates (ID, FileName, Name, Description, UploadedByTVDBUsersID, Created)
                SELECT ID, FileName, Name, Description, UploadedByTVDBUsersID, Created FROM dbo.Templates WITH (HOLDLOCK TABLOCKX)')
        GO
        SET IDENTITY_INSERT dbo.Tmp_Templates OFF
        GO
        DROP TABLE dbo.Templates
        GO
        EXECUTE sp_rename N'dbo.Tmp_Templates', N'Templates', 'OBJECT' 
        GO
        ALTER TABLE dbo.Templates ADD CONSTRAINT
            PK__Templates__499219E9 PRIMARY KEY CLUSTERED 
            (
            ID
            )
        GO
        PRINT N'  Templates ADD isHidden'
    COMMIT
END

Results in error:

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 'TRANSACTION'. Caution: Changing any part of an object name could break scripts and stored procedures.

Update:
Excluding the IF statement wrapping the transaction this SQL is generated by Microsoft SQL Server Management Studio.

If I remove the wrapping IF statement then everything works, but I only need the change to happen if the field isn't already there. How can I make the IF statement work properly?

Um... why the -1 and the vote to close?

Upvotes: 2

Views: 662

Answers (3)

Justin808
Justin808

Reputation: 21522

I had to wrap each part of the transaction in an IF statement so the GOs were not embedded in an IF statement. The following TSQL works just fine. The transaction updates the schema as expected.

BEGIN TRANSACTION
GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        CREATE TABLE dbo.Tmp_Templates
            (
            ID int NOT NULL IDENTITY (1, 1),
            isHidden bit NULL,
            FileName nvarchar(255) NOT NULL,
            Name nvarchar(255) NOT NULL,
            Description nvarchar(1024) NULL,
            UploadedByTVDBUsersID int NOT NULL,
            Created datetime NOT NULL
            )
        ALTER TABLE dbo.Tmp_Templates ADD PRIMARY KEY (ID)
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        SET IDENTITY_INSERT dbo.Tmp_Templates ON
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        IF EXISTS(SELECT * FROM dbo.Templates)
             EXEC('INSERT INTO dbo.Tmp_Templates (ID, FileName, Name, Description, UploadedByTVDBUsersID, Created)
                SELECT ID, FileName, Name, Description, UploadedByTVDBUsersID, Created FROM dbo.Templates WITH (HOLDLOCK TABLOCKX)')
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        SET IDENTITY_INSERT dbo.Tmp_Templates OFF
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        DROP TABLE dbo.Templates
    END
    GO
    IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
    BEGIN
        EXECUTE sp_rename N'dbo.Tmp_Templates', N'Templates', 'OBJECT' 
        PRINT N'  Templates ADD isHidden'
    END
    GO
COMMIT

Upvotes: 2

Ivan Bianko
Ivan Bianko

Reputation: 1769

first GO statement split your query to

IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE Name=N'isHidden' AND Object_ID=Object_ID(N'Templates'))
BEGIN
    BEGIN TRANSACTION
    //error - END missing

And there is BEGIN keyword without END. You need to remove GO statement.

UPDATE:

IF 1 = 1
BEGIN
    SELECT * FROM someTable
    GO
END

also generate Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'someTable'.

Upvotes: 0

Ivan Marcin
Ivan Marcin

Reputation: 81

SqlServer won't allow you to use sp_rename inside the transaction since it could break things badly.

You can drop and add the table again, on in your case, you can also use a temporale table to do the query work, truncate the old table, and move rows from temp into Templates.

Sample temp table
CREATE TABLE #myTempTable
(
  DummyField1 INT,
  DummyField2 VARCHAR(20)
)

Reference http://msdn.microsoft.com/en-us/library/ms188351.aspx

Upvotes: -1

Related Questions