Control of flow around "CREATE PROCEDURE"

Hi there.
I am trying to write a single script to create some stored procedures. One
of the stored procedures however, refers to a database which may or may not
be present on the server. In the case of that database NOT being present, I
would like to create the stored procedure with different contents (as the
original contents cause script errors when the missing database os referred
to). However, I'm having trouble controlling the flow of execution in the
script around CREATE PROCEDURE as it needs to be the first instruction in a
batch.
Basically I'd like to do something like this:
Use SomeOtherDatabase
GO
IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
CREATE PROCEDURE p_MyStoredProc
AS
SELECT * FROM MyDatabaseName.dbo.SomeTable
GO
ELSE --The database doesn't exist
CREATE PROCEDURE p_MyStoredProc
AS
PRINT 'The Database doesnt exist on this server'
GO
The reason I want to take this approach is to avoid script errors when the
script is run on servers where that database is missing.
Any ideas how I should go about this?
Any help would be much appreciated!!Len,
Kinda questionable approach. Try something like this using Dynamic SQL:
IF DB_ID('MyDatabaseName') IS NOT NULL
EXEC('CREATE PROCEDURE ...')
ELSE
EXEC('CREATE PROCEDURE ...')
Also see Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"len" <len@.discussions.microsoft.com> wrote in message
news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
> Hi there.
> I am trying to write a single script to create some stored procedures. One
> of the stored procedures however, refers to a database which may or may
> not
> be present on the server. In the case of that database NOT being present,
> I
> would like to create the stored procedure with different contents (as the
> original contents cause script errors when the missing database os
> referred
> to). However, I'm having trouble controlling the flow of execution in the
> script around CREATE PROCEDURE as it needs to be the first instruction in
> a
> batch.
> Basically I'd like to do something like this:
> Use SomeOtherDatabase
> GO
> IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
> CREATE PROCEDURE p_MyStoredProc
> AS
> SELECT * FROM MyDatabaseName.dbo.SomeTable
> GO
> ELSE --The database doesn't exist
> CREATE PROCEDURE p_MyStoredProc
> AS
> PRINT 'The Database doesnt exist on this server'
> GO
> The reason I want to take this approach is to avoid script errors when the
> script is run on servers where that database is missing.
> Any ideas how I should go about this?
> Any help would be much appreciated!!|||Why not just fix the code that is calling the wrong proc? Seems like an
unusual architecture if neither your client code or your procs will
know whether a database exists or not.
Where possible I find it better to reference other databases only in
views and then write procs against the views. That way views act as
your database indirection and the database names are hard-coded in as
few places as possible.
David Portas
SQL Server MVP
--|||Perfect - thanks! - I had tried dynamic SQL but got stuck on sp_executesql a
s
my stored proc was over 4000 chars long - Erland's article covers this thoug
h
"Jerry Spivey" wrote:

> Len,
> Kinda questionable approach. Try something like this using Dynamic SQL:
> IF DB_ID('MyDatabaseName') IS NOT NULL
> EXEC('CREATE PROCEDURE ...')
> ELSE
> EXEC('CREATE PROCEDURE ...')
> Also see Erland's article:
> http://www.sommarskog.se/dynamic_sql.html
> HTH
> Jerry
> "len" <len@.discussions.microsoft.com> wrote in message
> news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
>
>|||I'm not too happy with the architecture myself! Unfortunately it's a legacy
thing whereby my principal aim was just to minimize the number of scripts
needed to install some additional stored procs.
"David Portas" wrote:

> Why not just fix the code that is calling the wrong proc? Seems like an
> unusual architecture if neither your client code or your procs will
> know whether a database exists or not.
> Where possible I find it better to reference other databases only in
> views and then write procs against the views. That way views act as
> your database indirection and the database names are hard-coded in as
> few places as possible.
> --
> David Portas
> SQL Server MVP
> --
>