Trying to call a SP from another SP passing param

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to call a SP from another SP passing param

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-25 : 03:52:54

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,
Cèsar

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 04:37:05

"how can I do it in this part of the code?"

You can't

You will have to cursor round the relevant rows and call the Sproc for each one, or prepare a table of appropriate rows which the Sproc can process as a batch.

Kristen

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-25 : 04:53:12

Hi Kristen,
I don’ t absolutely know how to do what you said, could you write a simple example of that in my case please? This is the SP which I want to call:


USE dataBase3
GO

ALTER PROC UpdateOldRows 
@Ant As tinyint, @User_id As int, @Date As smalldatetime  
As


SET NOCOUNT ON

UPDATE OffersPublished_Control
SET Weekly_Antiquity = @Ant 
WHERE (User_num = @User_id) And (Date > (@Date - 7)) And (Date < @Date) 

SET NOCOUNT OFF 
GO

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 05:35:40

Seems to me it would just be easier to do an update.

Something like


UPDATE U
SET Weekly_Antiquity = 
    CASE WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, X.Start_Date)), @Date) < 7 THEN 1 
    WHEN DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, X.Start_Date)), @Date) > 2880 THEN 10
    END
FROM OffersPublished_Control AS OPC
JOIN
(
SELECT User_id, us2.Start_Date
(
 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 
WHERE    DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) < 7 
      OR DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, us2.Start_Date)), @Date) > 2880
) AS X
    ON X.User_id = OPC.User_num AND (OPC.Date > (X.Start_Date - 7)) And (OPC.Date < X.Start_Date )

That could no doubt be improved and optimised, but its just using your first query as the basis for an update

Backup first!

Kristen

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-25 : 10:28:30

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 (although I don' t use it yet). In this way I will not have to update rows after the expected date.

Thank you

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-25 : 12:13:09

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?


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

  SELECT @Date, us.User_id, us.Number_of_Offers, Monthly_Antiquity = CASE....

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-25 : 12:36:03

isnull(us.Number_of_Offers,0)

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-25 : 12:51:03

Ok, thank you!

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 05:47:12

Pedantic point: I recommend using COALESCE() instead of IsNull()