OSQL and GO

Question

Post reply

OSQL and GO

MrBaseball34

SSCrazy

Points: 2515

October 11, 2006 at 3:14 pm

#192709

When running a script through osql, we are having problems with the GO statement.

I need to do something like this:

PRINT 'Install SP SYSetTrigger'
DROP PROCEDURE SYSetTrigger
GO

CREATE PROCEDURE SYSetTrigger(@TriggerChar VarChar(15), 
                                              @TriggerValue  VarChar(3) = 'ON')
AS
BEGIN
  /* procedure code goes here */
END    
    
PRINT 'Install SP SYGetvTrigger'
DROP PROCEDURE SYGetvTrigger
GO

CREATE PROCEDURE SYGetvTrigger(@TriggerValue Int OUTPUT)
AS
BEGIN
  /* procedure code goes here */
END

However, the first procedure is not getting created because of the GO.

If I remove the GO's, I get the error:

"CREATE/ALTER PROCEDURE must be first statememtn in a query batch"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 1

Does the procedure you are trying to drop already exist?

What does the actual error say?

These things could be helpful indetermining what is actually going wrong

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

hrishi_des Newbie Points: 3 More actions · Answer 2

write it in following way.

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

IF

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

BEGIN

DROP PROCEDURE [SYSetTrigger]

END

ELSE

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[SYSetTrigger](@TriggerChar VarChar(15),

@TriggerValue VarChar(3) = ''ON'')AS

BEGIN

SET @TriggerValue = ''t''

/* procedure code goes here */

END '

END

thanks,

Hrishikesh

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 3

Hrishikesh - That won't create the procedure if it already exists, it'll just drop it.

I'm working on trying out some other costructs this morning and will fill you in on it later today.

vadba SSChampion Points: 11133 More actions · Answer 4

From a command prompt, I executed osql on my TEST database, using your code stored in a file called osql_test.sql. Two GOs were missing from your posted example - they are bold in the code below. I added some code so that the SPROCs would do something. I also added code (courtesy of Jeff Moden) to check for the existence of the the procs before dropping them.

osql -S MyServer -d TEST -E -i osql_test.sql

Here is osql_test.sql:

PRINT 'Install SP SYSetTrigger'

IF OBJECT_ID('SYSetTrigger') IS NOT NULL

AND OBJECTPROPERTY(OBJECT_ID('SYSetTrigger'),'IsProcedure') = 1

DROP PROCEDURE SYSetTrigger

GO

CREATE PROCEDURE SYSetTrigger(@TriggerChar VarChar(15),

@TriggerValue VarChar(3) = 'ON')

AS

BEGIN

DECLARE @x int

SET @x = 1

END

GO

PRINT 'Install SP SYGetvTrigger'

GO

IF OBJECT_ID('SYGetvTrigger') IS NOT NULL

AND OBJECTPROPERTY(OBJECT_ID('SYGetvTrigger'),'IsProcedure') = 1

DROP PROCEDURE SYGetvTrigger

GO

CREATE PROCEDURE SYGetvTrigger(@TriggerValue Int OUTPUT)

AS

BEGIN

DECLARE @x int

SET @x = 1

END

GO

MD-372277 SSCommitted Points: 1930 More actions · Answer 5

Hrishikesh statement will not create the stored procedure if it already exists because of the else statement, remove this and the script will work.