如何在SQL Server（2008）SP中使用集合作为参数？

So, I have this stored procedure (let's call it SP1) that gets as parameters two strings (say string1 and string2)

所以,我有这个存储过程(让我们称之为SP1)作为参数获取两个字符串(比如string1和string2)

However, I need to call this stored procedure a lot from my code, so in order to make things faster, I was thinking of doing it in bulk. Collecting all of the parameters into a collection of some sort, and then send this.

但是,我需要从我的代码中调用这个存储过程很多,所以为了加快速度,我正在考虑批量执行它。将所有参数收集到某种集合中,然后发送。

From what I understand I need to use a DataTable on the code side, and a custom table type as the parameter on the SQL Server side - ok, cool. But...now what?

根据我的理解,我需要在代码端使用DataTable,并将自定义表类型作为SQL Server端的参数 - 好的,很酷。但是......现在怎么样?

But... how do I get from there to the point where I actually go

但是......我怎么从那里到达我真正去的地步

EXEC SP1 string1, string2 or something along those lines?

3 个解决方案

#1

Not sure whether this is what you like to achieve, instead of parsing those two string parameters, you would like to get a table holding all of the string row? If so, you could you use UDF within SP,

不确定这是否是你想要实现的,而不是解析那两个字符串参数,你想得到一个包含所有字符串行的表吗?如果是这样,你可以在SP中使用UDF,

Check here:

CREATE FUNCTION [dbo].[Name]
     (
     @parameter
     )
RETURNS @Table TABLE 
    (
    col1 varchar(50),
    col2 varchar(50)
    )
AS
BEGIN
**the query that inserts each records to TABLE**
END

Then using this [Name] UDF in your SP

然后在SP中使用此[Name] UDF

#2

Create a table type

创建表类型

CREATE TYPE Strings AS TABLE ( String1 VARCHAR(50), String2 VARCHAR(50) )

Alter your procedure to accept table type as input

更改您的过程以接受表类型作为输入

ALTER PROCEDURE Usp_procname (@strings STRINGS Readonly)
AS
..

To call the procedure

调用该程序

DECLARE @strings STRINGS

INSERT INTO @strings
            (String1,String2)
SELECT 'String1','String2'
UNION ALL
SELECT 'String3','String4'
UNION ALL
SELECT 'String5','String6'

EXEC Usp_procname @strings

By this way you can pass more than one string in a single call. Make sure you update the logic inside the procedure to handle more than one string

通过这种方式,您可以在一次调用中传递多个字符串。确保更新过程中的逻辑以处理多个字符串

#3

In a case like this one, I usually concatenate strings and split them on the server side or pass even an xml if I have multiple columns. Sql it's very fast when processing xml. You can try all the methods and check the processing time and after that choose the best method.

在这样的情况下,我通常连接字符串并在服务器端拆分它们,或者如果我有多个列则传递一个xml。 Sql处理xml时速度非常快。您可以尝试所有方法并检查处理时间,然后选择最佳方法。

#1