SQL Server script error(Procedure)

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

General SQL Server Forums

New to SQL Server Programming

SQL Server script error(Procedure)

Author

Topic

raghukraman
Starting Member

2 Posts

Posted - 2010-11-02 : 10:30:28

Hi,

I want to run the sql script(drop existing proc and create create the new proc), if my current database in 'ABC'.
Here is the script i've.

Script:
===========================

if (upper(db_name())='ABC')
begin

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dummy_proc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dummy_proc]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure dummy_proc
@p_mass_request_id as bigint = 1 OUTPUT
AS
DECLARE
@v_new_mass_request_id AS bigint,
@requestID AS varchar(14)

BEGIN
select 'hello world'
end

end

---- SQL SCRIPT END

When i run, i'm getting the following errors.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dummy_proc'.
Msg 102, Level 15, State 1, Procedure dummy_proc, Line 14
Incorrect syntax near 'end'.

if i exclude the 1st 2 statment and the last 'end' statement, it works fine.

Any idea why? I think there is some problem with the GO Statements.

Basically, i want to check in the current database name is 'ABC', then drop the proc. if already exists, recreate the proc.

Please help me on this.

Thanks,
Raghu.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-02 : 11:33:57

The problem is that you can't but a GO in the body of a BEGIN/END block. Normally one would explicitly set the db with a USE statement rather than check which db you are in.

EDIT:
like this:


use ABC

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dummy_proc]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[dummy_proc]
GO

create procedure dummy_proc
@p_mass_request_id as bigint = 1 OUTPUT
AS

DECLARE
@v_new_mass_request_id AS bigint,
@requestID AS varchar(14)

BEGIN
	select 'hello world'
end

go

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources