Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to call a SP from another SP passing parameters 1

Status
Not open for further replies.
ericnet

ericnet

Programmer
Mar 29, 2006
106
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)

Code: 
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 [b]and call the SP (UpdateOldRows) passing it three parameters (1, us.User_id, @Date)[/b]
    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 [b]and call the SP (UpdateOldRows) passing it three parameters (2, us.User_id, @Date)[/b]  
    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 [b]and call the SP (UpdateOldRows) passing it three parameters (3, us.User_id, @Date)[/b] 
    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 [b]and call the SP (UpdateOldRows) passing it three parameters (4, us.User_id, @Date)[/b]  
    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 [b]and call the SP (UpdateOldRows) passing it three parameters (5, us.User_id, @Date)[/b] 
    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 [b]and call the SP (UpdateOldRows) passing it three parameters (6, us.User_id, @Date)[/b] 
    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 [b]and call the SP (UpdateOldRows) passing it three parameters (7, us.User_id, @Date)[/b] 
    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 [b]and call the SP (UpdateOldRows) passing it three parameters (8, us.User_id, @Date)[/b] 
    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 [b]and call the SP (UpdateOldRows) passing it three parameters (9, us.User_id, @Date)[/b] 
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 2880 THEN 10 [b]and call the SP (UpdateOldRows) passing it three parameters (10, us.User_id, @Date)[/b]          
   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,
Eric
 
Sort by date Sort by votes
erm,

you can't embed a stored procedure call within a select statement, your best bet would be to build a set of if statements to run your 2nd sproc after your select statement...


--------------------
Procrastinate Now!
 
Upvote 0 Downvote
I think I am going to consider to don't have to call the SP, and istead insert a 'future' value which will not change

Anyway thank you
 
Upvote 0 Downvote
Please one more question:
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?

Code: 
...
  INSERT INTO OffersPublished_Control (Date, User_num, [b]OffersPublished[/b], Monthly_Antiquity, Weekly_Antiquity)

  SELECT @Date, us.User_id, [b]us.Number_of_Offers[/b], Monthly_Antiquity = CASE...
 
Upvote 0 Downvote
coalesce(fieldname, 0)

--------------------
Procrastinate Now!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Shane_SixOne
  • Locked
  • Question
RDLC Parameters
Replies
0
Views
307
Shane_SixOne
Shane_SixOne
bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
390
bharons
bharons
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
462
Andrzejek
Andrzejek
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
795
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
814
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top