Post reply

create procedure

  • June 5, 2008 at 10:46 am

    #99149

    Hi guys,

    I have a procedure needs to used in both SQL2000 and SQL2005, but the codes are different for these two versions. Can this procedure be compiled in a way that for SQL2000 it compliles block 1 and for SQL2005 it compiles block 2?

    I thought I can do like this:

    $if @IsSQL2000 = 1

    /*block1*/

    $else

    /*block2*/

    But it doesn't work....

    thanks,

    JH

  • June 5, 2008 at 10:59 am

    #824968

    Not that I know of. The CREATE PROCEDURE needs to be the first command in a batch. You could perhaps call this dynamically

    declare @cmd1, @cmd2

    select @cmd1='create procedure sql2k as sxxx'

    select @cmd2='create procedure sql2k5 as sxxx'

    if version = 2000 (need to check this in the registry, or compat level)

    exec @cmd1

    else

    exec @cmd2

    You could drive this by compiling from the command line and possibly sending in the correct file from VCS depending on the version. Not sure it's worth it. might be easier to manage as a separate branch in VCS.

  • June 5, 2008 at 11:22 am

    #824993

    The only thing I could think of is to literally create the SP's with different code if you're on different versions. So use your IF...THEN logic to create different SP's.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • June 5, 2008 at 11:33 am

    #825007

    good idea, thanks Steve!

Viewing 4 posts - 1 through 4 (of 4 total)

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