Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Syntax Error in IF Statement

Author  Topic 

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2010-12-06 : 14:01:04
Hi everyone,

I'm having some trouble with the following statement. I'm getting an incorrect syntax near 'OFF' at line 3 and also at the 'END' portion at the bottom. I'm guessing it has to do something with the 'GO' keyword but if I were to remove them, I'd then have issues with the create procedure statement. Does anyone have an idea of how to work around this?


IF 1 = 1
BEGIN
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
-- =============================================
-- Description: Test proc
-- =============================================
CREATE PROCEDURE [dbo].[TestProc]
AS
BEGIN
SELECT 1
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO

DROP TABLE #tmpErrors

END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-06 : 14:28:51
Take out all the GO statements

From Books On Line

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.



Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2010-12-06 : 16:30:07
jim,
if I remove all the GO statements then my stored proc definition begins to complain about syntax errors. Could you provide an example of how I could include a stored proc definition inside an IF statement like I have?
Go to Top of Page

MuadDBA

628 Posts

Posted - 2010-12-06 : 16:34:50
Generally you should not create a proc inside an if block. But if you really really need to do it, you have to use dynamic SQL. Here is another similar discussion:

http://bytes.com/topic/sql-server/answers/501165-create-procedure-if-block

Go to Top of Page

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2010-12-06 : 16:48:12
Wow that stinks. Well thanks for the info guys!
Go to Top of Page
   

- Advertisement -