存储过程中的子句:如何传递值

I want to write a SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter.

我想编写一个SQL Server 2005存储过程，它将从用户表中选择并返回作为参数传递给存储过程的一些userids的用户记录。

How to do this ?

怎么做呢?

I can pass the user ids as a string separated by comma. So that I can use the

我可以将用户id作为用逗号分隔的字符串传递。这样我就可以用

select * 
from users 
where userid in (userids)

E.g. : I want to select records for id's 5,6,7,8,9

我想为id的5、6、7、8、9选择记录

How to write the stored procedure ?

如何编写存储过程?

7 个解决方案

#1

For SQL Server 2005, check out Erland Sommarskog's excellent Arrays and Lists in SQL Server 2005 article which shows some techniques how to deal with lists and arrays in SQL Server 2005 (he also has another article for SQL Server 2000).

对于SQL Server 2005，请查看Erland Sommarskog的优秀数组和SQL Server 2005文章中的列表，该文章展示了如何在SQL Server 2005中处理列表和数组的技术(他还为SQL Server 2000提供了另一篇文章)。

If you could upgrade to SQL Server 2008, you can use the new feature called "table valued parameter":

如果您可以升级到SQL Server 2008，您可以使用名为“表值参数”的新特性:

First, create a user-defined table type

首先，创建一个用户定义的表类型

CREATE TYPE dbo.MyUserIDs AS TABLE (UserID INT NOT NULL)

Secondly, use that table type in your stored procedure as a parameter:

其次，在存储过程中使用该表类型作为参数:

CREATE PROC proc_GetUsers @UserIDTable MyUserIDs READONLY 
AS
SELECT * FROM dbo.Users
    WHERE userid IN (SELECT UserID FROM @UserIDTable)

See details here.

在这里看到的细节。

Marc

马克

#2

Just use it like this will work

就像这样用就可以了

Create procedure sp_DoctorList 
@userid varchar(100)
as 
begin
exec ('select * from doctor where userid in ( '+ @userid +' )')
end

#3

you could use dynamic sql. Pass the in statement to a Sql SP via a variable and concatenate it into a query in the SQL and execute using sp_execute sql

您可以使用动态sql。通过变量将in语句传递给Sql SP，并将其连接到Sql中的查询中，并使用sp_execute Sql执行

create procedure myproc(@clause varchar(100)) as 
begin
  exec sp_executesql 'select * from users where userid in ( ' + @clause +' )'
end

#4

Assuming T-SQL, you can use this nice function (that returns a table).

假设T-SQL，您可以使用这个很好的函数(返回一个表)。

DROP FUNCTION sp_ConvertStringToTable
GO
CREATE FUNCTION sp_ConvertStringToTable(@list ntext)
      RETURNS @tbl TABLE (Position INT IDENTITY(1, 1) NOT NULL,
                          Value INT NOT NULL) AS
   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @str      nvarchar(4000),
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(' ', @tmpstr)
         WHILE @pos > 0
         BEGIN
            SET @str = substring(@tmpstr, 1, @pos - 1)
            INSERT @tbl (Value) VALUES(convert(int, @str))
            SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
            SET @pos = charindex(' ', @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      IF ltrim(rtrim(@leftover)) <> ''
         INSERT @tbl (Value) VALUES(convert(int, @leftover))

      RETURN
   END   
GO

In this way:

以这种方式:

SELECT * FROM Users 
WHERE userid IN 
( SELECT Value FROM sp_ConvertStringToTable('1 2 3') )

You can change the stored function to work with comma separated strings instead of space separated ones.

您可以使用逗号分隔的字符串来更改存储的函数，而不是使用空格分隔的字符串。

If you don't want / can't use a stored function you can include the code of it inside the stored procedure where needed.

如果您不想/不能使用存储函数，您可以在需要时将它的代码包含在存储过程中。

EDIT: this is incredibly more performant than the string concatenation.

编辑:这比字符串连接的性能要好得多。

#5

see my previous answer to this

看看我之前的回答。

this is the best source:

这是最好的来源:

http://www.sommarskog.se/arrays-in-sql.html

create a split function, and use it like:

创建一个分割函数，并像:

SELECT
    *
    FROM YourTable  y
    INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value

I prefer the number table approach

我喜欢数字表方法。

For this method to work, you need to do this one time table setup:

要让这个方法工作，你需要做这个时间表设置:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, create this function:

设置好数表后，创建以下函数:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO

You can now easily split a CSV string into a table and join on it:

现在，您可以轻松地将CSV字符串分割为一个表并将其连接到:

select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')

OUTPUT:

输出:

ListValue
-----------------------
1
2
3
4
5
6777

(6 row(s) affected)

Your can pass in a CSV string into a procedure and process only rows for the given IDs:

您可以将一个CSV字符串传递到一个过程中，并只处理给定id的行:

SELECT
    y.*
    FROM YourTable y
        INNER JOIN dbo.FN_ListToTable(',',@GivenCSV) s ON y.ID=s.ListValue

#6

try this this works for me

试试这个对我有用

DECLARE @InClause NVARCHAR(100)
SET @InClause = 'tom,dick,harry'
DECLARE @SafeInClause NVARCHAR(100)
SET @SafeInClause = ',' + @InClause + ',' 
SELECT * FROM myTable WHERE PATINDEX(',' + myColumn + ',', @SafeInClause) > 0

#7

-1

You can also use Find_IN_SET instead of IN. See the query below

您还可以使用Find_IN_SET而不是IN。请参阅下面的查询

create procedure myproc(IN in_user_ids varchar(100)) 
begin
   select * from users where FIND_IN_SET(userid, in_user_ids);
end

#1