Casting separates the comma into an integer to be used in SQL "IN"

Question

I pass the numbers, separated by commas, as a string to the stored procedure, the parameters are used in the IN key, however when executing SP (with parameters like "1,2") this gives me an error:

Conversion error when converting varchar value '1,2' to internal data type.

Stored procedure:

 ALTER PROCEDURE [Reports].[LocationSummary] @dtFrom VARCHAR(MAX), @dtTo VARCHAR(MAX), @passTypeId VARCHAR(MAX), @passCategoryId VARCHAR(MAX), @passTransId VARCHAR(MAX), @printOption VARCHAR(MAX), @printLocationId VARCHAR(MAX) AS BEGIN SELECT Admins.PLocations.ArabicName AS PLocationA, Admins.PLocations.EnglishName AS PLocationE, SUM(CASE WHEN Port.Info.Printed = 1 THEN 1 ELSE 0 END) AS Printed, SUM(CASE WHEN Port.Info.Printed = 0 THEN 0 ELSE 1 END) AS NotPrinted, SUM(CASE WHEN Port.PortPrintLog.PortId IS NOT NULL THEN 1 ELSE 0 END) AS Reprinted FROM Port.Info INNER JOIN Port.PortRequests ON Port.Info.PassRequestId = Port.PortRequests.ID LEFT JOIN Port.PortDefinitions ON Port.PortRequests.PassDefinitionID = Port.PortDefinitions.ID INNER JOIN Admins.PortCategories ON Port.PortDefinitions.PassCategoryId = Admins.PortCategories.ID INNER JOIN Admins.PortTypes ON Port.PortDefinitions.PassTypeId = Admins.PortTypes.ID INNER JOIN Admins.PortTransactionTypes ON Port.PortDefinitions.PassTransactionTypeId = Admins.PortTransactionTypes.ID INNER JOIN Admins.PLocations ON Port.Info.PrintLocationID = Admins.PLocations.ID LEFT JOIN Port.PortPrintLog ON Port.PortPrintLog.PortId = Port.Info.ID WHERE (@dtFrom IS NOT NULL AND Port.PortRequests.IssuanceDate IS NOT NULL AND Port.PortRequests.IssuanceDate >= @dtFrom) AND (@dtTo IS NOT NULL AND Port.PortRequests.ExpiryDate IS NOT NULL AND Port.PortRequests.ExpiryDate <= @dtTo) AND ((Admins.PortTypes.ID IN (CAST(@passTypeId AS INT))) AND (Admins.PortCategories.ID IN (CAST(@passCategoryId))) AND (Admins.PortTransactionTypes.ID IN (CAST(@passTransId))) AND (Port.Info.PrintLocationID IN (CAST(@PrintLocationId AS INT)))) GROUP BY Admins.PrintLocations.ArabicName, Admins.PrintLocations.EnglishName 

How can I avoid this error? Should I use the Split function for a string? Can I see an example of this feature?

Answer 1

@jyparask gave a dynamic request, you can use it otherwise to try to implement the following logic. you have more than one column, otherwise I made changes.

 Declare @var1 as varchar(2000) Declare @var2 as varchar(2000) Set @var1 = '31,39,41,45' Set @var2 = ',' + @var1 + ',' select * from Table1 Where Charindex(','+cast(Col1 as varchar)+',', @var2) > 0 

Answer 2

You may have dynamic sql:

 ALTER PROCEDURE [Reports].[LocationSummary] @dtFrom VARCHAR(MAX), @dtTo VARCHAR(MAX), @passTypeId VARCHAR(MAX), @passCategoryId VARCHAR(MAX), @passTransId VARCHAR(MAX), @printOption VARCHAR(MAX), @printLocationId VARCHAR(MAX) AS DECLARE @SQL AS VARCHAR(MAX) SET @SQL = 'SELECT Admins.PLocations.ArabicName AS PLocationA, Admins.PLocations.EnglishName AS PLocationE, SUM(CASE WHEN Port.Info.Printed = 1 THEN 1 ELSE 0 END) AS Printed, SUM(CASE WHEN Port.Info.Printed = 0 THEN 0 ELSE 1 END) AS NotPrinted, SUM(CASE WHEN Port.PortPrintLog.PortId IS NOT NULL THEN 1 ELSE 0 END) AS Reprinted FROM Port.Info INNER JOIN Port.PortRequests ON Port.Info.PassRequestId = Port.PortRequests.ID LEFT JOIN Port.PortDefinitions ON Port.PortRequests.PassDefinitionID = Port.PortDefinitions.ID INNER JOIN Admins.PortCategories ON Port.PortDefinitions.PassCategoryId = Admins.PortCategories.ID INNER JOIN Admins.PortTypes ON Port.PortDefinitions.PassTypeId = Admins.PortTypes.ID INNER JOIN Admins.PortTransactionTypes ON Port.PortDefinitions.PassTransactionTypeId = Admins.PortTransactionTypes.ID INNER JOIN Admins.PLocations ON Port.Info.PrintLocationID = Admins.PLocations.ID LEFT JOIN Port.PortPrintLog ON Port.PortPrintLog.PortId = Port.Info.ID WHERE (@dtFrom IS NOT NULL AND Port.PortRequests.IssuanceDate IS NOT NULL AND Port.PortRequests.IssuanceDate >= @dtFrom) AND (@dtTo IS NOT NULL AND Port.PortRequests.ExpiryDate IS NOT NULL AND Port.PortRequests.ExpiryDate <= @dtTo) AND ((Admins.PortTypes.ID IN (' + @passTypeId + ')) AND (Admins.PortCategories.ID IN (' + @passCategoryId + ')) AND (Admins.PortTransactionTypes.ID IN (' + @passTransId + ')) AND (Port.Info.PrintLocationID IN (' + @PrintLocationId + '))) GROUP BY Admins.PrintLocations.ArabicName, Admins.PrintLocations.EnglishName' EXEC (@SQL) 

Answer 3

 ALTER PROCEDURE [Reports].[LocationSummary] @dtFrom VARCHAR(MAX), @dtTo VARCHAR(MAX), @passTypeId VARCHAR(MAX), @passCategoryId VARCHAR(MAX), @passTransId VARCHAR(MAX), @printOption VARCHAR(MAX), @printLocationId VARCHAR(MAX) AS BEGIN SELECT Admins.PLocations.ArabicName AS PLocationA, Admins.PLocations.EnglishName AS PLocationE, SUM(CASE WHEN Port.Info.Printed = 1 THEN 1 ELSE 0 END) AS Printed, SUM(CASE WHEN Port.Info.Printed = 0 THEN 0 ELSE 1 END) AS NotPrinted, SUM(CASE WHEN Port.PortPrintLog.PortId IS NOT NULL THEN 1 ELSE 0 END) AS Reprinted FROM Port.Info INNER JOIN Port.PortRequests ON Port.Info.PassRequestId = Port.PortRequests.ID LEFT JOIN Port.PortDefinitions ON Port.PortRequests.PassDefinitionID = Port.PortDefinitions.ID INNER JOIN Admins.PortCategories ON Port.PortDefinitions.PassCategoryId = Admins.PortCategories.ID INNER JOIN Admins.PortTypes ON Port.PortDefinitions.PassTypeId = Admins.PortTypes.ID INNER JOIN Admins.PortTransactionTypes ON Port.PortDefinitions.PassTransactionTypeId = Admins.PortTransactionTypes.ID INNER JOIN Admins.PLocations ON Port.Info.PrintLocationID = Admins.PLocations.ID LEFT JOIN Port.PortPrintLog ON Port.PortPrintLog.PortId = Port.Info.ID WHERE (@dtFrom IS NOT NULL AND Port.PortRequests.IssuanceDate IS NOT NULL AND Port.PortRequests.IssuanceDate >= @dtFrom) AND (@dtTo IS NOT NULL AND Port.PortRequests.ExpiryDate IS NOT NULL AND Port.PortRequests.ExpiryDate <= @dtTo) AND Admins.PortTypes.ID IN (@passTypeId) AND Admins.PortCategories.ID IN (@passCategoryId) AND Admins.PortTransactionTypes.ID IN (@passTransId) AND Port.Info.PrintLocationID IN (@PrintLocationId) GROUP BY Admins.PrintLocations.ArabicName, Admins.PrintLocations.EnglishName 

casting to int will cause an error when passing 1,2 in the parameter. do not drop it.

Answer 4

Make sure your comma-separated list has spaces:

 (1,2) 

does not match

 (1, 2)