create sql server script that optionally creates stored procs based on value of a variable

I need to run a script 2 times, once for a local DB and once for a "server" DB, that will perform certain operations on both DB's, but only on the local DB for other opeartions. I would like to set a variable at the top then run script, reset it, then run script again, but my "if" statements are invalid syntax because the create SP statemtent is not playing well with the begin/end and the if statements:

declare @isCorpDB as bit
set @isCorpDB = 0


if (@isCorpDB=1)
begin
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'QT_Checklist_GetApprovalQuestions' AND type = 'P')
   DROP PROCEDURE QT_Checklist_GetApprovalQuestions
GO  
CREATE PROCEDURE [dbo].[QT_Checklist_GetApprovalQuestions] AS
Select * From QT_Sites_ApprovedVolumes_Questions
GO

end


--- gives error - "Incorrect syntax near GO"
bmutchAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
create two scripts would be better otherwise you need to use dynamic sql like this

declare @isCorpDB as bit
set @isCorpDB = 0


if (@isCorpDB=1)
begin
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'QT_Checklist_GetApprovalQuestions' AND type = 'P')
   DROP PROCEDURE QT_Checklist_GetApprovalQuestions
EXEC( '
CREATE PROCEDURE [dbo].[QT_Checklist_GetApprovalQuestions] AS
Select * From QT_Sites_ApprovedVolumes_Questions
GO  ' )

end
0
 
bmutchAuthor Commented:
a pain either way, dynamic sql or 2 scripts with a lot of duplication, thanks again M$.
0
 
bmutchAuthor Commented:
I guess that's all I'm going to get on this, thanks aneeshattingal
0
All Courses

From novice to tech pro — start learning today.