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
)
AS
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
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))
GO