Passing a varchar full of comma delimited values to a SQL Server

Passing a varchar full of comma delimited values to a SQL Server

Declare @Ids varchar(50)

Set @Ids = ',1,2,3,5,4,6,7,98,470,'

SELECT [pk_Opr_Id]

,[Opr_Code]

,[Opr_Name]

,[Opr_NavigationURL]

,[Opr_ParentTranNo]

,[Opr_SeqNo]

,[Opr_IsAdmin]

,[Opr_IsAddPage]

,[Opr_IsListPage]

,[Opr_IsReportPage]

FROM [dbo].[Sys_Operation]

where Charindex(','+cast([pk_Opr_Id] as varchar)+',', @Ids) > 0

the condition is that your string value must start and end with ‘,'

or you can also create function

CREATE FUNCTION [dbo].[FUNC_SplitOrderIDs]

(

@OrderList varchar(500)

)

RETURNS

@ParsedList table

(

OrderID int

)

BEGIN

DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','

SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''

BEGIN

WHILE @Pos > 0

BEGIN

SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))

IF @OrderID <> ''

BEGIN

INSERT INTO @ParsedList (OrderID)

VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion

END

SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)

SET @Pos = CHARINDEX(',', @OrderList, 1)

END

RETURN

END

=====================================================

Declare @Ids varchar(50)

Set @Ids = ',1,2,3,5,4,6,7,98,470,'

SELECT [pk_Opr_Id]

,[Opr_Code]

,[Opr_Name]

,[Opr_NavigationURL]

,[Opr_ParentTranNo]

,[Opr_SeqNo]

,[Opr_IsAdmin]

,[Opr_IsAddPage]

,[Opr_IsListPage]

,[Opr_IsReportPage]

FROM [dbo].[Sys_Operation]

WHERE pk_Opr_Id IN (SELECT OrderID FROM [dbo].[FUNC_SplitOrderIDs] (@Ids))