October 11, 2006 at 3:14 pm
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"
October 11, 2006 at 10:58 pm
October 12, 2006 at 5:05 am
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
October 12, 2006 at 7:27 am
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.
October 12, 2006 at 8:59 am
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
October 15, 2006 at 7:20 pm
Hrishikesh statement will not create the stored procedure if it already exists because of the else statement, remove this and the script will work.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply