how can I make this stored procedure repeat itself with a changing parameter?

Question

I've got this stored procedure I use in SSRS as a report query. I'd like to repeat this for every day of the month so I could output an entire months' dates min/max times in a table. the parameters are set in SSRS, I created a separate table with the dates numbers. When I used multiple choice in SSRS it gave me a conversion error. (the DATEPART(dd, Events.FieldTime) = @date wants an INT but the commas seem to mess it up) What should I use? tips? ideas?

@userID VARCHAR(200), @date NVARCHAR(50), @month INT AS
--Main SELECT clause that displays users and their entry/exit times from departments 3, 7, 10, 14, 15
SELECT DISTINCT TOP 10000
       [PTUsers].[Name]

      --subquery that finds the min time on the selected door on the given date
      ,(SELECT TOP 1
        MIN([Events].[FieldTime]) AS MinEntry

        FROM
        [Events], [PTDoors] pd, [PTUsers] ptu
        INNER JOIN
        [PTUsers] ON [Events].[GlobalIndex1] = [ptu].[GlobalRecord]

        WHERE [Events].[GlobalIndex1] = [ptu].[GlobalRecord]
        AND   [Events].[RecordIndex2] = 14
        --AND ptu.Name = PTUsers.Name
        AND (DATEPART(dd, [Events].[FieldTime]) = @date)
        AND (DATEPART(mm, [Events].[FieldTime]) = @month)
        AND (DATEPART(yyyy, [Events].[FieldTime]) = DATEPART(year, GETDATE()) )
        --AND (pd.Panel = 0)
        --AND (PTUsers.Panel = 0)

        GROUP BY
        [PTUsers].[Name]
        ) AS MinTime

        --subquery that finds the min time on the selected door one day prior to the given date
      ,(SELECT TOP 1
        MIN([Events].[FieldTime]) AS MinEntryYest

        FROM
        [Events], [PTDoors] pd, [PTUsers] ptu
        INNER JOIN
        [PTUsers] ON [Events].[GlobalIndex1] = [ptu].[GlobalRecord]

        WHERE 
            [Events].[GlobalIndex1] = [ptu].[GlobalRecord]
        AND [Events].[RecordIndex2] = 14
        --AND ptu.Name = PTUsers.Name
        AND (DATEPART(dd, [Events].[FieldTime]) =@date-1)
        AND (DATEPART(mm, [Events].[FieldTime]) = @month)
        AND (DATEPART(yyyy, [Events].[FieldTime]) = DATEPART(year, GETDATE()) )
        AND (DATEPART(HH, [Events].[FieldTime]) >= 21 )
        --AND (pd.Panel = 0)
        --AND (PTUsers.Panel = 0)

        GROUP BY
        [PTUsers].[Name]
        ) AS MinTimeYest

        --subquery that finds the max time on the selected door on the given date
        ,(SELECT TOP 1
        MAX([Events].[FieldTime]) AS MaxEntry

        FROM
        [Events], [PTDoors] pd1, [PTUsers] ptu 
        INNER JOIN
        [PTUsers] ON [Events].[GlobalIndex1] = [ptu].[GlobalRecord]

        WHERE [Events].[GlobalIndex1] = [ptu].[GlobalRecord]
        AND   [Events].[RecordIndex2] = 15
        --AND ptu.Name = PTUsers.Name
        AND (DATEPART(dd, [Events].[FieldTime]) = @date)
        AND (DATEPART(mm, [Events].[FieldTime]) = @month)
        AND (DATEPART(yyyy, [Events].[FieldTime]) = DATEPART(year, GETDATE()) )
        --AND (pd1.Panel = 0)
        --AND (PTUsers.Panel = 0)


        GROUP BY
        [PTUsers].[Name]
        ) AS MaxTime


  FROM [dbo].[DateTbl], [dbo].[Events]
  INNER JOIN
  [dbo].[PTUsers] ON PTUsers.GlobalRecord = [Events].[GlobalIndex1]
  INNER JOIN
  [dbo].[PTDoors] ON [Events].[RecordIndex2] = PTDoors.PTDoorsID

WHERE
        (PTUsers.Panel = 0) 
    AND (PTDoors.Panel = 0) 
    AND (PTDoors.PTDoorsID = 14 OR PTDoors.PTDoorsID = 15)
    AND (PTUsers.AccessLevel IN (3,7,10,14,15))
    AND (DATEPART(month, [Events].[FieldTime]) = (@month))
    AND (PTUsers.GlobalRecord) IN (SELECT Item FROM dbo.SplitUser(@userID, ','))
    AND (DateTbl.DateNR) IN (SELECT Item FROM dbo.SplitDates(@date, ','))

ORDER BY MaxTime DESC 

Answer 1

you can use split function (TSQL or CLR) which returns TABLE. Then you can join with that result set. Here you can find how to implement it in TSQL: http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/