Recursive stored procedure

Transact-SQL
,
1

Hello everyone,

I have the following stored procedure:

CREATE PROCEDURE [dbo].[REP_MISSING_DATES]
(@UserID NVARCHAR(50))

AS
BEGIN

WITH dates AS
(SELECT CAST('20150601' AS DATE) AS insdate
 UNION ALL
 SELECT DATEADD(dd, 1, insdate)
 FROM dates
 WHERE DATEADD(dd, 1, insdate) <= getdate()-1
 ),
dateNames AS (SELECT dates.insdate, tblUsers.userid AS User1
FROM dates CROSS JOIN tblUsers
where userid=@UserID)

SELECT insdate,User1 
FROM dateNames
WHERE insdate NOT IN (SELECT TaskDate FROM tblTask INNER JOIN dateNames ON dateNames.User1=tblTask.UserID) AND
      datename(dw,insdate)<>'Saturday' AND 
      datename(dw,insdate)<>'Sunday'
GROUP BY insdate, User1

END

The above stored procedure works fine when UserID is given as parameter (for one user). How can I change the code in order to get the insdate for every UserID?

Thank you in advance.

2

Get a string-splitter such as the one described here. Then, change the "where userid = @UserID" to this

userid IN (SELECT Item FROM dbo.DelimitedSplit8K(@UserID,','))

You can also join to the output of the delimitedSplit8K, which perhaps might be a little safer and better.

3

Thank you for your answer!

I tried using DelimitedSplit8K with @userID=user1,user2,.... userN but i'm only get user1 and user2.
I used function DelimitedSplit8K as given.

Any suggestions?

4

How long is each userId? Perhaps the NVARCHAR(50) is not sufficient to hold all the userid's in the comma-delimited string? If that is the case change the NVARCHAR(50) to something longer.

CREATE PROCEDURE [dbo].[REP_MISSING_DATES]
(@UserID NVARCHAR(4000))
5

You are right...I forgot to change the size of @UserID!

Ok... now I have the following issues.

  1. If one user of the input string hasn't missed a date then I get an empty result
    2 In case all the users I enter as input have missed dates, I only get the getdate()-1 as a missing date for everyone.