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

)

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

 

 

 

Ebook Download
View all
Learn
View all