Trying to call a SP from another SP passing parameters

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

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'

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...

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.