Passing Single Parameter that Contains Multiple ID's

Question

My problem is that I am trying to pass a single parameter inside of a query. From the backend side I already have all the ID's that I need to pass over to the stored procedure but I get an error "Conversion failed when converting the varchar value '"1,2,3,4"' to data type int." from MSSSQL. I have looked into Table-Valued Parameters but I dont that will work in due to it being readonly and I would need to populate it with data coming from the website with the ID's.

This may indeed be a duplicate of another question, but I have tried doing my research and haven't came across anything that seemed to work for this situation.

Stored Procedure

USE [DB_9AC5B9_ScoreCardViewer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetEmployeeNotionalMargin_RevenueData]

    @ReportType varchar(255),
    @StartDate Date,
    @EndDate Date,
    @EmployeeID varchar(255)

AS
BEGIN

    SET NOCOUNT ON;

    SELECT 
        DATENAME(MONTH, [Dates].[DateStamp]) AS [MonthName], [Employees].[FirstName], 
        [ScoreCard].[NotionalMargin], [ScoreCard].[Revenue]
    FROM [ScoreCard]
        INNER JOIN [Employees] ON [ScoreCard].[EmployeeID] = [Employees].[EmployeeID] 
        INNER JOIN [Dates] ON [ScoreCard].[DateID] = [Dates].[DateID]
    WHERE 
        [Dates].[ReportType] = @ReportType AND [Dates].[DateStamp] BETWEEN  @StartDate AND @EndDate
        AND [ScoreCard].[EmployeeID] IN 
        (
            @EmployeeID
        )
    ORDER BY [Dates].[DateStamp], [Employees].[EmployeeID]

END

http://stackoverflow.com/questions/2773539/sqlcasting-a-string-to-ids-with-in-clause — Rafa Paez, Feb 16 '14 at 02:00
@ta.speot.is - Per the top portion of OP I have tried google and even talked about why I dont think a TVP will work, would you mind telling me how a readonly table can be used to insert data into? Not trying to sound rude, I just dont know how. — Jon H, Feb 16 '14 at 02:08
@Rafa - Im looking into the link you posted and seeing if any of this will work, will comment back — Jon H, Feb 16 '14 at 02:09
Table valued parameters are not read only. Only when passed to stored procedures as input. If they were always read only then there's be no point to having a table valued parameter... See [Putting my new table variable type to use](http://www.techrepublic.com/blog/the-enterprise-cloud/passing-table-valued-parameters-in-sql-server-2008/) and one example for C# http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters (there's a few different ways to pass the data from C# - I prefer `IEnumerable`) — ta.speot.is, Feb 16 '14 at 02:15

Dave C · Accepted Answer · 2014-02-16T02:34:42.087

4

You can accomplish this using dynamic SQL.

USE [DB_9AC5B9_ScoreCardViewer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetEmployeeNotionalMargin_RevenueData]

    @ReportType varchar(255),
    @StartDate Date,
    @EndDate Date,
    @EmployeeID varchar(255)

AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX), @PARAMDEF NVARCHAR(MAX)

    SET NOCOUNT ON;

    SET @SQL='SELECT 
            DATENAME(MONTH, [Dates].[DateStamp]) AS [MonthName], [Employees].[FirstName], 
            [ScoreCard].[NotionalMargin], [ScoreCard].[Revenue]
        FROM [ScoreCard]
            INNER JOIN [Employees] ON [ScoreCard].[EmployeeID] = [Employees].[EmployeeID] 
            INNER JOIN [Dates] ON [ScoreCard].[DateID] = [Dates].[DateID]
        WHERE 
            [Dates].[ReportType] = @ReportType AND [Dates].[DateStamp] BETWEEN  @StartDate AND @EndDate
            AND [ScoreCard].[EmployeeID] IN 
            (
                '+@EmployeeID+'
            )
        ORDER BY [Dates].[DateStamp], [Employees].[EmployeeID]'
    SET @PARAMDEF=N'@ReportType varchar(255), @StartDate Date, @EndDate Date'
    PRINT @SQL
    EXEC sp_executesql @SQL, @PARAMDEF, @ReportType=@ReportType, @StartDate=@StartDate, @EndDate=@EndDate

END

edited Feb 16 '14 at 02:34

answered Feb 16 '14 at 02:15

Dave C

7,272
1
19
30

-1 This won't work because `EXEC` has no idea what `@ReportType` is, and the same goes for `@StartDate` and `@EndDate`. You need to use `sp_executesql` to provide values for those parameters. – ta.speot.is Feb 16 '14 at 02:17
1

Bah, I was focused on the @employeeid... it's late. Thank you. – Dave C Feb 16 '14 at 02:20
Works, care to explain why this works? Or whats going on. I dont want to just copy and paste – Jon H Feb 16 '14 at 02:25
Because your EmployeeID variable was an varchar, and the ScoreCard.EmployeeID is an int, it cannot properly evaluate the contents of the varchar you provided in the IN statement. So we wrap the query up in a big string SQL, and embed the Employee ID value into that string (in-line). You can test this by dropping 'PRINT @SQL' right before the EXEC... and if you copy and paste that into a new SSMS window, it would look like IN (1,2,3,4) -- which can evaluate correctly. The remainder, using sp_execute sql is used to "run" the newly constructed SQL variable as if it was a real command. – Dave C Feb 16 '14 at 02:29
The paramdef variable is just to specify what ReportType, StartDate and EndDate are to the newly made command, and the rest is us passing your values into the sp_execute. – Dave C Feb 16 '14 at 02:30

Passing Single Parameter that Contains Multiple ID's

Stored Procedure

1 Answers1