How to pass multiple strings in one function parameter in SQL Server

uurity8g 于 2023-10-15 发布在 SQL Server

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

I need to find a way to give multiple values to one parameter.

An example to explain what I want to achieve:

CREATE TABLE Students 
(
    code int,
    name varchar(20)
);

INSERT INTO Students VALUES (1,'abc');
INSERT INTO Students VALUES (2,'def');
INSERT INTO Students VALUES (3,'ghi');
INSERT INTO Students VALUES (4,'jkl');

SELECT * FROM students;

CREATE FUNCTION dbo.GetCount (@name VARCHAR(8000)) 
RETURNS INT
AS
BEGIN
    DECLARE @TheResultCount INT;  

    SELECT @TheResultCount = COUNT(*) 
    FROM Students 
    WHERE name IN (@name);

    RETURN @TheResultCount;
END;

SELECT dbo.GetCount('abc,def,ghi,jkl');

How do I achieve this requirement?

sql-server

来源：https://stackoverflow.com/questions/77144329/how-to-pass-multiple-strings-in-one-function-parameter-in-sql-server

2条答案

按热度按时间

hpxqektj1#

Passing multiple values to a function really should use a table-valued parameter.

As an example you would have a suitably named table type defined:

create type NameList as table(Name Varchar(20));

And your function would use it like so:

create function dbo.GetCount (@Names NameList readonly) 
returns Int
as
begin
    return (select Count(*) from Students where name in (select name from @Names));
end;

And you would use the function like so:

declare @MyNames Namelist;
insert into @MyNames values('abc'), ('ghi')

select dbo.GetCount(@MyNames);

If however you really must pass a comma-delimited string, your function would look like:

create or alter function dbo.GetCount (@Names Varchar(max)) 
returns Int
as
begin
  return (
    select Count(*) from Students 
    where name in (select value from String_Split(@Names, ','))
  );
end;

赞(0）回复(0）举报 2023-10-15

qojgxg4l2#

you can use string_split if you're using SQL Server 2016 and later.

CREATE FUNCTION dbo.GetCount(@name VARCHAR(8000)) 
RETURNS INT
AS
BEGIN
RETURN ( 
    SELECT COUNT(*) 
    FROM Students e
    WHERE EXISTS (
        SELECT 1
        FROM string_split(@name, ',') s
        WHERE s.value = e.name
    )
)
END

for older versions of SQL Server you can do this :

CREATE FUNCTION dbo.GetCount(@name VARCHAR(8000)) 
RETURNS INT
AS
BEGIN
RETURN ( 
    SELECT COUNT(*) 
    FROM Students e
    WHERE EXISTS (
        SELECT 1
        FROM (
            SELECT n.name 
            FROM (SELECT CAST(('<X>'+ REPLACE(@name,',' ,'</X><X>')+'</X>') AS XML) Split ) s
            CROSS APPLY
            ( 
                SELECT d.s.value('.','VARCHAR(MAX)') as name 
                FROM s.Split.nodes('X') as d(s)
            ) n
    
        ) s
        WHERE s.name = e.name
    )
)
END

赞(0）回复(0）举报 2023-10-15

我来回答

How to pass multiple strings in one function parameter in SQL Server

2条答案

相关问题

热门标签

最新问答