Casting comma separated string to integer to be used in "IN" SQL
sql-server, sql-server-2008, t-sql
Solution
@jyparask has given dynamic query , you can use that otherwise try to implement following logic. you have more then one column otherwise i have done the 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
Problem
I pass a comma separated numbers as string to a stored procedure, the parameters are used in `IN` keyword, however when I execute the SP (with parameters like "1,2") it gives me an error: Conversion failed when converting the varchar value '1,2' to data type int. The stored procedure is: ``` 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 that error? should I use a function to `Split` the string? Can I see some example for that function?