IF and TRANSACTION problem
in [Programming SQL]
|
From: David DB on 22 Jul 2008 05:43 Hi, Have a problem that is probably easy to fix, but I cannot understand why this happens. The following code fails because of the GO's in the code: -------------------------- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExtraUserSettings]') AND type in (N'U')) Begin BEGIN TRANSACTION GO CREATE TABLE [dbo].[ExtraUserSettings]( [UserID] [int] NOT NULL, [WhatType] [smallint] NOT NULL, [Setting] [nvarchar](30) NOT NULL, [Value] [ntext] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[ExtraUserSettings] ADD CONSTRAINT [FK_ExtraUserSettings_Users] FOREIGN KEY ( [UserID] ) REFERENCES [dbo].[Users] ( [UserID] ) ON UPDATE CASCADE ON DELETE CASCADE GO CREATE NONCLUSTERED INDEX IDX_UserExtraSettings_UserID ON dbo.ExtraUserSettings ( UserID ) ON [PRIMARY] GO COMMIT TRANSACTION GO End ----------------------------------- Error: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'TRANSACTION'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'End'. ---------------------------------- I just want it to execute when the table does not exist. Taking away the GO's makes it compile, but I'm not sure if that will affect the logic of the batches.... David
From: vinu on 22 Jul 2008 05:58 David remove the GO from your code read BOL for more info on GO vinu "David DB" <er_fortsatt(a)hotmail.com> wrote in message news:OnyK5%2396IHA.1468(a)TK2MSFTNGP05.phx.gbl... > Hi, > > Have a problem that is probably easy to fix, but I cannot understand why > this happens. > > The following code fails because of the GO's in the code: > > -------------------------- > IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = > OBJECT_ID(N'[dbo].[ExtraUserSettings]') AND type in (N'U')) > > Begin > > BEGIN TRANSACTION > > GO > > CREATE TABLE [dbo].[ExtraUserSettings]( > > [UserID] [int] NOT NULL, > > [WhatType] [smallint] NOT NULL, > > [Setting] [nvarchar](30) NOT NULL, > > [Value] [ntext] NOT NULL > > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > > GO > > ALTER TABLE [dbo].[ExtraUserSettings] ADD CONSTRAINT > > [FK_ExtraUserSettings_Users] FOREIGN KEY > > ( > > [UserID] > > ) REFERENCES [dbo].[Users] > > ( > > [UserID] > > ) ON UPDATE CASCADE > > ON DELETE CASCADE > > GO > > CREATE NONCLUSTERED INDEX IDX_UserExtraSettings_UserID ON > dbo.ExtraUserSettings > > ( > > UserID > > ) ON [PRIMARY] > > GO > > COMMIT TRANSACTION > > GO > > End > > ----------------------------------- > > Error: > > Msg 102, Level 15, State 1, Line 3 > > Incorrect syntax near 'TRANSACTION'. > > Msg 102, Level 15, State 1, Line 1 > > Incorrect syntax near 'End'. > > ---------------------------------- > > I just want it to execute when the table does not exist. Taking away the > GO's makes it compile, but I'm not sure if that will affect the logic of > the batches.... > > David > >
From: David DB on 22 Jul 2008 06:39 Hi Winu, But will that make any problems ? Thinking that the table is not created before altered and such David "vinu" <vinu.t.1976(a)gmail.com> wrote in message news:uiPqxF%236IHA.4292(a)TK2MSFTNGP02.phx.gbl... > David > > remove the GO from your code > read BOL for more info on GO > > vinu > > "David DB" <er_fortsatt(a)hotmail.com> wrote in message > news:OnyK5%2396IHA.1468(a)TK2MSFTNGP05.phx.gbl... >> Hi, >> >> Have a problem that is probably easy to fix, but I cannot understand why >> this happens. >> >> The following code fails because of the GO's in the code: >> >> -------------------------- >> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = >> OBJECT_ID(N'[dbo].[ExtraUserSettings]') AND type in (N'U')) >> >> Begin >> >> BEGIN TRANSACTION >> >> GO >> >> CREATE TABLE [dbo].[ExtraUserSettings]( >> >> [UserID] [int] NOT NULL, >> >> [WhatType] [smallint] NOT NULL, >> >> [Setting] [nvarchar](30) NOT NULL, >> >> [Value] [ntext] NOT NULL >> >> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] >> >> GO >> >> ALTER TABLE [dbo].[ExtraUserSettings] ADD CONSTRAINT >> >> [FK_ExtraUserSettings_Users] FOREIGN KEY >> >> ( >> >> [UserID] >> >> ) REFERENCES [dbo].[Users] >> >> ( >> >> [UserID] >> >> ) ON UPDATE CASCADE >> >> ON DELETE CASCADE >> >> GO >> >> CREATE NONCLUSTERED INDEX IDX_UserExtraSettings_UserID ON >> dbo.ExtraUserSettings >> >> ( >> >> UserID >> >> ) ON [PRIMARY] >> >> GO >> >> COMMIT TRANSACTION >> >> GO >> >> End >> >> ----------------------------------- >> >> Error: >> >> Msg 102, Level 15, State 1, Line 3 >> >> Incorrect syntax near 'TRANSACTION'. >> >> Msg 102, Level 15, State 1, Line 1 >> >> Incorrect syntax near 'End'. >> >> ---------------------------------- >> >> I just want it to execute when the table does not exist. Taking away the >> GO's makes it compile, but I'm not sure if that will affect the logic of >> the batches.... >> >> David >> >> > >
From: Andrew J. Kelly on 22 Jul 2008 09:29 You can not have a GO inside a BEGIN END block. TSQL is procedural and line 1 will always execute before line 2 and so on regardless of the GO's so the table will be created before it is altered assuming there are no errors. If this is SQL 2005 you should look at using TRY CATCH block to handle any errors. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "David DB" <er_fortsatt(a)hotmail.com> wrote in message news:%23JlO5d%236IHA.2220(a)TK2MSFTNGP06.phx.gbl... > Hi Winu, > > But will that make any problems ? Thinking that the table is not created > before altered and such > > David > > "vinu" <vinu.t.1976(a)gmail.com> wrote in message > news:uiPqxF%236IHA.4292(a)TK2MSFTNGP02.phx.gbl... >> David >> >> remove the GO from your code >> read BOL for more info on GO >> >> vinu >> >> "David DB" <er_fortsatt(a)hotmail.com> wrote in message >> news:OnyK5%2396IHA.1468(a)TK2MSFTNGP05.phx.gbl... >>> Hi, >>> >>> Have a problem that is probably easy to fix, but I cannot understand why >>> this happens. >>> >>> The following code fails because of the GO's in the code: >>> >>> -------------------------- >>> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = >>> OBJECT_ID(N'[dbo].[ExtraUserSettings]') AND type in (N'U')) >>> >>> Begin >>> >>> BEGIN TRANSACTION >>> >>> GO >>> >>> CREATE TABLE [dbo].[ExtraUserSettings]( >>> >>> [UserID] [int] NOT NULL, >>> >>> [WhatType] [smallint] NOT NULL, >>> >>> [Setting] [nvarchar](30) NOT NULL, >>> >>> [Value] [ntext] NOT NULL >>> >>> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] >>> >>> GO >>> >>> ALTER TABLE [dbo].[ExtraUserSettings] ADD CONSTRAINT >>> >>> [FK_ExtraUserSettings_Users] FOREIGN KEY >>> >>> ( >>> >>> [UserID] >>> >>> ) REFERENCES [dbo].[Users] >>> >>> ( >>> >>> [UserID] >>> >>> ) ON UPDATE CASCADE >>> >>> ON DELETE CASCADE >>> >>> GO >>> >>> CREATE NONCLUSTERED INDEX IDX_UserExtraSettings_UserID ON >>> dbo.ExtraUserSettings >>> >>> ( >>> >>> UserID >>> >>> ) ON [PRIMARY] >>> >>> GO >>> >>> COMMIT TRANSACTION >>> >>> GO >>> >>> End >>> >>> ----------------------------------- >>> >>> Error: >>> >>> Msg 102, Level 15, State 1, Line 3 >>> >>> Incorrect syntax near 'TRANSACTION'. >>> >>> Msg 102, Level 15, State 1, Line 1 >>> >>> Incorrect syntax near 'End'. >>> >>> ---------------------------------- >>> >>> I just want it to execute when the table does not exist. Taking away the >>> GO's makes it compile, but I'm not sure if that will affect the logic of >>> the batches.... >>> >>> David >>> >>> >> >> > >
From: Hugo Kornelis on 22 Jul 2008 11:58
On Tue, 22 Jul 2008 11:43:42 +0200, David DB wrote: >Hi, > >Have a problem that is probably easy to fix, but I cannot understand why >this happens. Hi David, I didn't check whether you'll get batch compilation problems if you leave out the GO's as others have suggested. You probably won't. However, you can also fix this problem another way - by removing the BEGIN and END that surrounds the code. A BEGIN ... END block has to be in a single batch. And GO ends the batch, so that was the problem. You don't need to remove the BEGIN TRANSACTION and COMMIT TRANSACTION; a transaction *can* span multiple batches. -- Hugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis |