Incorrect syntax near ';'. - SQL Server Forums

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)

Incorrect syntax near ';'.

Author

Topic

vince144
Starting Member

5 Posts

Posted - 2008-07-31 : 13:29:06

Hi can someone tell me what is wrong with the below script, I'm passing in a variable using sqlcmd which is no problem (i can print out the variable and it was OK) but I keep getting "Incorrect syntax near ';'."

I want to conditionally create the store procedure and I can't figure out what the syntax is. Is the GO presenting a problem? Any help greatly appreciated.


IF ('$(VER)' = '10')
BEGIN
	PRINT N'Creating store procedure...'

	IF OBJECT_ID(N'dbo.usp_TestExists', N'P') IS NOT NULL
		DROP PROCEDURE [dbo].[usp_TestExists];
	GO
	
	CREATE PROCEDURE [dbo].[usp_TestExists]
		(@compId INT,
		 @exists BIT OUTPUT)
	AS
	BEGIN
		SET NOCOUNT ON;
		IF EXISTS(SELECT [CompRecID] FROM [dbo].[Company] WHERE [CompID] = @compId)
			SET @exists = 1;
		ELSE
			SET @exists = 0;
	END
	GO
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 13:35:52

You dont need the braces around parameters.Also can you tell which line the error is coming?

vince144
Starting Member

5 Posts

Posted - 2008-07-31 : 13:49:01

Thanks for the reply, what braces are you referring to, and the only error I got from sqlcmd from the command line is

Msg 102, Level 15, State 1, Server ROBOT, Line 14
Incorrect syntax near ';'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-31 : 13:49:34

I think that you have to eliminate the GO's.

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 13:53:42

quote:
Originally posted by webfred

I think that you have to eliminate the GO's.

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

yeah that can be problem...didnt spot that. good catch

vince144
Starting Member

5 Posts

Posted - 2008-07-31 : 13:56:33

Thanks webfred I tried this as suggested


IF ('$(VER)' = '10')
BEGIN
	PRINT N'Creating store procedure...'

	IF OBJECT_ID(N'dbo.usp_TestExists', N'P') IS NOT NULL
		DROP PROCEDURE [dbo].[usp_TestExists];
	
	CREATE PROCEDURE [dbo].[usp_TestExists]
		(@compId INT,
		 @exists BIT OUTPUT)
	AS
	BEGIN
		SET NOCOUNT ON;
		IF EXISTS(SELECT [CompRecID] FROM [dbo].[Company] WHERE [CompID] = @compId)
			SET @exists = 1;
		ELSE
			SET @exists = 0;
	END
END

but now get the following errors:


Incorrect syntax near the keyword 'PROCEDURE'.
Msg 137, Level 15, State 2, Server ROBOT, Line 22
Must declare the scalar variable "@compId".
Msg 137, Level 15, State 1, Server ROBOT, Line 23
Must declare the scalar variable "@exists".
Msg 137, Level 15, State 1, Server ROBOT, Line 25
Must declare the scalar variable "@exists".

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-31 : 14:17:17

sorry, no idea

You can try the statements unconditional in a query window to see what happens...

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

vince144
Starting Member

5 Posts

Posted - 2008-07-31 : 14:22:27

oh man :(

I tried it in the query window and got the following error

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

so I inserted the GO's and it worked :(


	PRINT N'Creating store procedure...'

	IF OBJECT_ID(N'dbo.usp_TestExists', N'P') IS NOT NULL
		DROP PROCEDURE [dbo].[usp_TestExists];
	GO

	CREATE PROCEDURE [dbo].[usp_TestExists]
		(@compId INT,
		 @exists BIT OUTPUT)
	AS
	BEGIN
		SET NOCOUNT ON;
		IF EXISTS(SELECT [CompRecID] FROM [dbo].[Company] WHERE [CompID] = @compId)
			SET @exists = 1;
		ELSE
			SET @exists = 0;
	END
	GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-31 : 16:22:29

So that means you cannot put an IF around it

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

vince144
Starting Member

5 Posts

Posted - 2008-07-31 : 16:36:57

How come? Does anyone know how to accomplish this? Basically I want to create a store procedure based on some flag?

genep
Starting Member

1 Post

Posted - 2008-10-28 : 12:19:09

Put your stored procedure in a variable (varchar(max) works well) and do "exec sp_executesql @variablename" You'll have to remove the GO to get it to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:21:24

quote:
Originally posted by genep

Put your stored procedure in a variable (varchar(max) works well) and do "exec sp_executesql @variablename" You'll have to remove the GO to get it to work.

why use dynamic sql here?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-28 : 12:48:01

quote:

why use dynamic sql here?

He has to to do what he wants (at first glance anyway) (which is to consider some logical expression) and then decide whether to make a stored proc or not. Because the CREATE/ALTER procedure statement needs to be the first statement in a batch. (and because IF statements can't cross batches)

You can indeed do as genep suggestion and go down the dynamic sql route. However, it's probably better not to

Visakh's question should have been : Why do you want to do this?

If you must do as you suggest it would be better to move the logic from the script and into your calling program You would then call the database, querying whether VER is 10

Depending on the output of that, you could then choose (in a bat file or whatever you are using) whether or not to call sqlcmd again with a straight-forward:


PRINT N'Creating store procedure...'

	IF OBJECT_ID(N'dbo.usp_TestExists', N'P') IS NOT NULL
		DROP PROCEDURE [dbo].[usp_TestExists];
	GO
	
	CREATE PROCEDURE [dbo].[usp_TestExists]
		(@compId INT,
		 @exists BIT OUTPUT)
	AS
	BEGIN
		SET NOCOUNT ON;
		IF EXISTS(SELECT [CompRecID] FROM [dbo].[Company] WHERE [CompID] = @compId)
			SET @exists = 1;
		ELSE
			SET @exists = 0;
	END
	GO

-------------
Charlie

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-28 : 12:48:16

yeah, it's a bit of a pain to use a script to conditionally create a stored procedure. The problem is conditional logic and control of flow statements cannot span batches yet a CREATE PROC statement needs to be in its own batch. One common way is as genep suggested.

Be One with the Optimizer
TG

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-28 : 12:59:14

In this case (with the very simple stored proc you posted, it's not too much trouble to implement it using dynamic sql. However, for a mure complicated stored proc it is generally much harder (you have to make sure you escape all your quotes etc).

If it was me I'd make the choice whether to create the stored proc or not in my application / batch file / whatever and then call a simple script with no dynamic sql.

However, if you want to use the dynamic sql method here's a simple example:


DECLARE @sql VARCHAR(MAX)

IF ('$(VER)' = '10')
BEGIN
	PRINT N'Creating store procedure...'

	IF OBJECT_ID(N'dbo.usp_TestExists', N'P') IS NOT NULL DROP PROCEDURE [dbo].[usp_TestExists];

	SET @sql = '
	
	CREATE PROCEDURE [dbo].[usp_TestExists]
		(@compId INT,
		 @exists BIT OUTPUT)
	AS
	BEGIN
		SET NOCOUNT ON;
		IF EXISTS(SELECT [CompRecID] FROM [dbo].[Company] WHERE [CompID] = @compId)
			SET @exists = 1;
		ELSE
			SET @exists = 0;
	END
	'

	EXEC (@sql)
END

NB: Im not sure what '$(VER)' means. When I try using it on my sql2005 instance it just returns '$(VER)' (are you expecting this to return an int?

Check out http://www.sommarskog.se/dynamic_sql.html for a very, very, good reference for all things dynamic.

-------------
Charlie

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-28 : 13:10:56

I think the '$(ver)' is sqlcmd mode syntax for variables. If this is for sqlcmd you can also conditionlly execute/call a seperate script file (-i switch) which could contain your CREATE PROC code.

Be One with the Optimizer
TG

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-28 : 13:15:54

Thanks TG

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources