CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jon H
Jon H

Reputation: 273

Passing Single Parameter that Contains Multiple ID's

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

Upvotes: 2

Views: 1335

Answers (1)

Dave C
Dave C

Reputation: 7392

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

Upvotes: 4

Related Questions