SQL Server : select where with multiple values

xurqigkl 于 2023-02-18 发布在 SQL Server

关注(0)|答案(2)|浏览(158)

I need to create a stored procedure that can check for all records matching an indefinite number of values.

So for example take the following simple statement;

SELECT * 
FROM some_table 
WHERE ID = @param1

However, @param1 should be able to take a string of comma-delimited values and check against all of them eg:

@param1 = '1,2,45,16,476,324,'

I imagine this would need to take the values and then turn them into a temporary table, and then somehow create a sub query to check against all the values in the temporary table. However my T-SQL skills aren't quite up to tackling that on my own just yet.

How can I do this, or is there a better way to do it?

sql-server

来源：https://stackoverflow.com/questions/28638442/sql-server-select-where-with-multiple-values

2条答案

按热度按时间

xqk2d5yq1#

There are many split function version online, if you just google SQL Server Split function you will get 100s of results back.

A quick fix without a function would look something like......

DECLARE @param1 VARCHAR(100) = '1,2,45,16,476,324'       
DECLARE @param1XML xml;

SELECT @param1XML = CONVERT(xml,' <root> <s>' 
                    + REPLACE(@param1, ',','</s> <s>') 
                    + '</s>   </root> ')

SELECT * 
FROM some_table 
WHERE ID IN (
             SELECT T.c.value('.','varchar(20)') AS Value
             FROM @param1XML.nodes('/root/s') T(c)
             )

Procedure

A proc would look something like...

CREATE PROCEDURE dbo.usp_SomeProc 
  @param1 VARCHAR(100)
AS
BEGIN
   SET NOCOUNT ON;
  DECLARE @param1XML xml; 

 SELECT @param1XML = CONVERT(xml,' <root> <s>' 
                    + REPLACE(@param1, ',','</s> <s>') 
                    + '</s>   </root> ')

    SELECT * 
    FROM some_table 
    WHERE ID IN (
    SELECT  T.c.value('.','varchar(20)')  AS Value
    FROM @param1XML.nodes('/root/s') T(c))

END

赞(0）回复(0）举报 2023-02-18

cngwdvgl2#

What about this solution - using XML query. Table variable in procedure is created for test only.

CREATE PROCEDURE dbo.spTestDelimitedParam 
(
  @param1 as varchar(max)
)
AS
BEGIN
  DECLARE @xml as xml
  DECLARE @delimiter as char(1)
  DECLARE @test table (id int, description varchar(20))

  insert into @test 
  values (1, 'Row ID = 1'), (11, 'Row ID = 11'), (3, 'Row ID = 3')

  SET @delimiter =','
  SET @xml = cast(('<X>'+replace(@param1,@delimiter ,'</X><X>')+'</X>') as xml)
  SELECT * 
  FROM @test 
  WHERE ID IN (SELECT N.value('.', 'int') as value FROM @xml.nodes('X') as T(N))
END

How it works:

exec dbo.spTestDelimitedParam '1,23,4,11,24456'

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server : select where with multiple values

2条答案

Procedure

相关问题

热门标签

最新问答