January 6, 2005 at 5:27 pm
My question is about statement control within a script. It's very convenient to have all the create table, procedures, etc. stuff in once script. However depending on what system I'm building I need to have the same stored procedure be a little different. For code management purposes its nice not to have to split these into different files; if something changes in one it may need to be changed in the other version. I was hoping to be able to declare a local variable and control script flow as shown below. However it looks like this can't be done for procedures. Any suggestions?
Thanks for the help.
Fred
--- beginning of script to create all stored procedures
DECLARE @TargetSystem varchar(20)
set @TargetSystem = 'LTS'
IF @TargetSystem = 'LTS'
BEGIN
CREATE PROCEDURE [dbo].[GetSomething]
@InSessionID bigint,
.... more procedure stuff for LTS target system
END
IF @TargetSystem = 'Central'
BEGIN
CREATE PROCEDURE [dbo].[GetSomething]
@InSessionID bigint,
.... more procedure stuff but needs to be different than the procedure for LTS.
END
January 10, 2005 at 8:00 am
This was removed by the editor as SPAM
January 10, 2005 at 8:53 am
Hi there,
If we can make a working assumption that most of the script is identical for both systems, then you might consider the following code snippet. In my case, I have a semi-custom proc for each unique DB the mainline code runs in. The mainline code is a few hundred lines long and most of it is identical for all DBs. Once some generic initialization work is completed then the following snippet is ran, followed by the remainder of the generic code.
-- If a procedure named CustomProcName exists then it will be called at this time. This allows us to externalize many custom hacks while the mainline code remains relatively generic.
IF EXISTS (SELECT 1
FROM sysobjects
WHERE name = 'CustomProcName' AND type = 'P')
BEGIN
EXEC CustomProcName
END
Your mileage from this will vary greatly depending on the number and complexity of the differences between the mainline code and the system-specific code. At some point, the best solution may well be to maintain two parallel implementations.
Hope this helps
Wayne
January 12, 2005 at 9:35 am
Wayne,
I not quite following your example for what I would like to do. I would like to have one stored procedures build script. However, depending on the target system I need one of the stored procedures to be slightly different. It seems like conditional processing and create procedure statements don't mix.
It looks like I probably will have to maintain the code in separate scripts.
Thanks,
Fred
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply