Stored Procedure Consolidation

  • I have two stored procedures with the same parameters : From Date & To Date.

    SP#1 - uses birth date

    SP#2 - uses hire date

    Both stored procedures return the same exact columns.

    I would like to consolidate this into just one stored procedure with an additional parameter, let's say @DateType. If user selects 1, then the stored procedure runs with birth date in the WHERE clause. If the user selects 2, then the stored procedure uses hire date in the WHERE clause.

    Is this possible? If so, could someone help me out with a small sample?

    Thank you in advance.

  • If you're just looking for a single SP to access the same data I'd just setup a 3rd SP to call one or the other...

    if your looking for just 1 sp the easiest would be to just replace the call to the sp's in the sample code with the actual script from each SP..

    --Switching the SP's

    create proc SwitchingSP

    @Parm1 int

    ,@Parm2 datetime

    ,@UseBDate bit

    AS

    if @UseBDate = 1

    begin

    exec SPwithBDate @Parm1, @Parm2

    end

    else

    begin

    exec SPwithOtherDate @Parm1, @Parm2

    end

    or dynamic sql I think this will work... but if your original SP is really really complicated or long I'd just go the first way...

    create proc MyProc

    @Parm1 int

    ,@Parm2 datetime

    ,@Type bit

    AS

    declare @sql as nvarchar(max)

    set @sql = N'select top 10 * from sys.tables where object_id = @ID and '

    if @Type = 1

    set @sql = @sql + N'Create_Date = @Date'

    else

    set @sql = @sql + N'modify_date = @Date'

    exec sp_executesql @sql, N'@Date Datetime, @ID int', @ID = @Parm1, @Date = @Parm2

    ---------------------------------------------------------------
    Mike Hahn - MCSomething someday:-)
    Right way to ask for help!!
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    I post so I can see my avatar :hehe:
    I want a personal webpage 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

  • If the created_date and modify date are not nullable, then please check this code.

    create procedure usp_switch_data

    @create_date datetime = null

    ,@modify_date datetime = null

    as

    if @create_date is not null or @modify_date is not null

    begin

    select * from mytable

    where created_date = isnull(@create_date, create_date)

    and modify_date = isnull(@modify_date, modify_date)

    end

  • Yes, we can call stored procedures inside stored procedures.

    see sample storedprocedure:

    Create Procedure Details

    (

    @FromDate datetime

    ,@ToDate datetime

    ,@DateType nchar(10)

    )

    AS

    BEGIN

    IF @DateType = 'Birth'

    EXEC procwithBirthDate @FromDate, @ToDate

    ELSE

    EXEC procWithHireDate @FromDate, @ToDate

    END

    I think it vl helps.

  • salliven (2/22/2013)


    If the created_date and modify date are not nullable, then please check this code.

    create procedure usp_switch_data

    @create_date datetime = null

    ,@modify_date datetime = null

    as

    if @create_date is not null or @modify_date is not null

    begin

    select * from mytable

    where created_date = isnull(@create_date, create_date)

    and modify_date = isnull(@modify_date, modify_date)

    end

    ISNULL() in where clauses is usually a performance killer in SQL Server.

  • I ended up going with the first suggestion by Want a cool Sig and it worked just as I needed.

    I will also try the others to see if I can make them work and understand them.

    Thank you everyone for your suggestions. I really appreciate the help.

    Have a great day,

    - nails

Viewing 6 posts - 1 through 5 (of 5 total)

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