SQL Server Performance

Trying to call a SP from another SP passing parameters

Discussion in 'T-SQL Performance Tuning for Developers' started by Cesar, Sep 25, 2007.

  1. Cesar New Member

    Hello, I have this SP, and I want to call another SP to update some data, how can I do it in this part of the code? (in bold) USE dataBase3
    GO

    Declare @Date As smalldatetime
    SET @Date = '15/09/2007' --date format dd/mm/yyyy

    Declare @FirstDayYear As smalldatetime
    SET @FirstDayYear = CAST('31/' + '12/' + CAST(YEAR(getdate()) - 1 AS VARCHAR(4)) AS smalldatetime)

    Declare @FirstDayMonth As smalldatetime
    SET @FirstDayMonth = CAST('01/' + CAST(MONTH(getdate()) AS VARCHAR(2)) + '/' + CAST(YEAR(getdate()) AS VARCHAR(4)) AS smalldatetime)


    SET NOCOUNT ON

    INSERT INTO OffersPublished_Control (Date, User_num, OffersPublished, Monthly_Antiquity, Weekly_Antiquity)

    SELECT @Date, us.User_id, us.Number_of_Offers, Monthly_Antiquity = CASE
    WHEN DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)) >= @FirstDayMonth THEN 11
    ELSE
    CASE WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) < 7 THEN 1
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) >= 7 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) <= 14 THEN 2
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) > 14 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) <= 30 THEN 3
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) > 30 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) <= 90 THEN 4
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) > 90 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) <= 180 THEN 5
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) > 180 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) <= 360 THEN 6
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) > 360 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) <= 720 THEN 7
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) > 720 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) <= 1440 THEN 8
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) > 1440 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) <= 2880 THEN 9
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @FirstDayMonth) > 2880 THEN 10
    END
    END, Weekly_Antiquity = CASE
    WHEN (DATEDIFF(dd, @FirstDayYear, @Date) % 7) = 0 THEN
    CASE WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) < 7 THEN 1 and call the SP (UpdateOldRows) passing it three parameters (1, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) >= 7 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) <= 14 THEN 2 and call the SP (UpdateOldRows) passing it three parameters (2, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 14 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) <= 30 THEN 3 and call the SP (UpdateOldRows) passing it three parameters (3, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 30 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) <= 90 THEN 4 and call the SP (UpdateOldRows) passing it three parameters (4, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 90 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) <= 180 THEN 5 and call the SP (UpdateOldRows) passing it three parameters (5, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 180 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) <= 360 THEN 6 and call the SP (UpdateOldRows) passing it three parameters (6, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 360 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) <= 720 THEN 7 and call the SP (UpdateOldRows) passing it three parameters (7, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 720 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) <= 1440 THEN 8 and call the SP (UpdateOldRows) passing it three parameters (8, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 1440 And DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) <= 2880 THEN 9 and call the SP (UpdateOldRows) passing it three parameters (9, us.User_id, @Date)
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 2880 THEN 10 and call the SP (UpdateOldRows) passing it three parameters (10, us.User_id, @Date)
    END
    ELSE NULL
    END



    from

    (
    select User_id, sum(offe.Number_of_Offers) As Number_of_Offers
    from Users As us
    LEFT JOIN
    (
    select User_num, count(*) As Number_of_Offers
    from Offers
    where DATEADD(dd, 0, DATEDIFF(dd, 0, Date)) = @Date And State IN(1, 2, 3)
    group by User_num
    ) offe
    on us.User_id = offe.User_num
    WHERE us.Start_State = 4 And us.Start_Date <= @Date
    GROUP BY us.User_id

    ) As us
    INNER JOIN Users as us2
    on us.User_id = us2.User_id


    SET NOCOUNT OFF

    GO And… By the way, but less important: Somebody knows another better alternative to the last INNER JOIN of my code ‘INNER JOIN Users as us2’ ? In this way now works, and the utility of this last inner join is to avoid having to aggregate (using some aggregate function) and/or grouping (using GROUP BY clause) the ‘Start_Date’ column from users table which I need to use in my select statement.
    Thank you,
    Cesar
  2. abin.thayyil New Member

    in the first place why u r using inner join at all coz u r getting the users already in the user table?Could u please explain
    to call another procedure 'exec UpdateOldRows '10,us.User_id, @Date'
  3. Cesar New Member

    Do you know how can I do it in my first posted code so that my OffersPublished column shows 0 value instead of Null value when none offer is counted by the current date and user selected?
    ...
    INSERT INTO OffersPublished_Control (Date, User_num, OffersPublished, Monthly_Antiquity, Weekly_Antiquity)

    SELECT @Date, us.User_id, us.Number_of_Offers, Monthly_Antiquity = CASE...
  4. Adriaan New Member

    You're trying to do 2 things at the same time, with potentially 10 different sets of parameters, and all in one query. And on the technical level, you want to execute an sp from a SELECT statement. I admire your enthousiasm.[H][:S][;)]
    The two things you want to do at the same time is to insert (INSERT statement) and update (to be done in the sp). There are no shortcuts here: you have to do separate INSERT and UPDATE calls.
    If you have ten different sets of parameters, which will result in different INSERT and/or UPDATE calls, then really the easiest way to make this happen is to write out those ten different pairs of INSERT and UPDATE calls. It may seem like you're producing a lot of redundant code, but if anyone besides yourself ever has to maintain this code, they will be forever grateful that you did.

Share This Page