如何使用同一个 SqlConnection 对象在多个 SqlCommand 中声明和使用 T-SQL 变量来执行多次插入？

Question

我想加载一个记录列表，给定可能很长的用户名列表（从一个到数千个用户名）。 忽略名称的选择方式，并假设无法从数据库中的任何现有数据确定它们。 这适用于 SQL Server 2005。

我特别希望避免使用 在 where 子句中包含数千个表达式的单个 select 语句，这会导致在 SqlCommand 对象的过长命令文本中（例如 ...其中 n='bob000001' 或 n='bob000002' 或 ... 或 n='bob003000'）。 听起来有道理吗？

我决定通过用用户名填充一个简单的表变量来执行选择，然后在表变量和带有用户数据的表之间执行选择/连接。

因此，我需要做的第一件事是填充表变量。 我在这里遇到一些问题：

• SQL Server 2008 之前的 T-SQL 语法对于在单个语句中将多行插入到表中来说非常冗长，需要类似于 多个选择和联合所有。
• 我没有使用 SS2005 的详细语法，甚至没有 SQL Server 2008 中可用的简洁语法，而是完全避免使用冗长的命令文本，而只是在单个连接上使用多个命令。
• 当我尝试在后续 SqlCommand 中使用表变量时，在一个 SqlCommand 中声明表变量会产生“必须声明标量变量”错误。
• 以任何方式涉及存储过程可能仍然涉及传递巨大的字符串，或者可能会阻止变量在存储过程的范围之外持续存在。 假设创建存储过程不是一个选项。

第三点确实是我现在正在努力解决的问题。 我见过一些例子，人们（声称）在单个 SqlCommand 中成功声明和使用变量而没有错误。 使用多个 SqlCommand 实例时如何实现这一点？ 我读到变量将在多个命令的单个连接中持续存在。 涉及交易可能会以某种方式有所帮助吗？

最后，请记住，我不想使用临时表； 这样做将提供一个简单的解决方案，但它也避免了我提出的有关变量和多个 SqlCommand 的问题； 但是，如果您确实认为这是最好的选择，请随意说出来。

以下是显示发生情况的代码片段：

public static List<Student> Load( SqlConnection conn, List<StudentID> usernames )
{
    //Create table variable
    SqlCommand  command = new SqlCommand( "declare @s table (id varchar(30))", conn );
    command.ExecuteNonQuery();

    //Populate a table variable with the usernames to load
    command = new SqlCommand( "insert into @s (id) values (@p)", conn );
    command.Parameters.Add( "@p", SqlDbType.VarChar );
    int len = usernames.Count;
    for (int i = 0; i < len; i++)
    {
        command.Parameters["@p"].Value = usernames[i].ToString();
        command.ExecuteNonQuery(); //ERROR: must declare scalar variable @s
    }

    //Select all students listed in the table variable
    command = new SqlCommand( "select StudentID, FName, LName, [etc.] from Student inner join @s on StudentID = @s.id order by StudentID", conn );

    //Execute the query to get the student info from the database.
    List<Student> students = new List<Student>()
    using(SqlDataReader reader = command.ExecuteReader())
    {
        //code to load results and populate students list
    }
    return students;
}

注意：我知道涉及参数的 SqlCommand 在内部调用存储过程，这通常会阻止跨多个 SqlCommand 持久保存变量，但声明表变量的第一个查询不涉及参数（即没有引用command.Parameters.AddWithValue），因此它应该在可能调用存储过程的后续命令的范围内。

编辑：要使用临时表，只需将 @s 更改为 #s 并将 declare @s table" 更改为 create表#s，这很好。人们可能还想在最后删除临时表，但这不是必需的。

Answer 1

使用在会话/连接（多次调用）期间持续存在的临时表。 表变量仅具有批处理的作用域，这基本上是一次调用。

Answer 2

冗长的命令文本有什么问题？ 我在一次调用中执行了几千字节的大查询。 SQL2005支持它，我认为它总是比往返更好。

为什么不创建这样的查询呢？

select StudentID, FName, LName, [etc.] from Student 
where StudentID in ('bob000001', 'bob000002', [etc.])

Answer 3

不确定这是否是最好的解决方案，但我相信您可以通过将多个命令包装在 BEGIN/END 块中来批处理多个命令，并使用单个调用来运行所有命令。

Answer 4

我不知道如何解决你的问题，但是我解决你的问题的替代方法是使用一个存储过程，该存储过程接受逗号分隔的 ID 列表，将这些 ID 插入临时表中，然后执行“SELECT WHERE IN”，例如（从另一个博客更改）：

CREATE PROC dbo.SelectStudents
(
    @StudentIdList varchar(8000)
)
AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE #StudentIdList (
        StudentId int,
    )

    DECLARE @StudentId varchar(10), @Pos int

    SET @StudentIdList = LTRIM(RTRIM(@StudentIdList)) + ','
    SET @Pos = CHARINDEX(',', @StudentIdList, 1)

    IF REPLACE(@StudentIdList, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @StudentId = LTRIM(RTRIM(LEFT(@StudentIdList, @Pos - 1)))
            IF @StudentId <> ''
            BEGIN
                INSERT INTO #StudentIdList (StudentId) VALUES (CAST(@StudentId AS int))
            END
            SET @StudentIdList = RIGHT(@StudentIdList, LEN(@StudentIdList) - @Pos)
            SET @Pos = CHARINDEX(',', @StudentIdList, 1)
        END
    END 

    SELECT  * 
    FROM    dbo.Students
    WHERE   StudentId IN
    (
        SELECT StudentId FROM #StudentIdList
    )
END

然后，您可以使用逗号分隔的 id 列表调用此存储过程：

exec dbo.SelectStudents '1,2'

您需要确保格式化的 id 列表不超过 8000 个字符（并对数据库进行多次调用如果是的话），但是这种方法会更高效，并且只需要一个大参数，而不是很长的命令文本 - 大量访问数据库来插入每个 Id 将极其慢。

存在类似的方法来将 Id 列表作为 Xml 传递（尽管我的偏好可能是为了简化分隔字符串）。

此外，即使出于某种原因您无法在数据库上创建存储过程，也应该可以使用上述技术。