如何从单个参数中获取多个值到 SQL Server 中的过程中 [英] How to get multi values from a single parameter into procedure in SQL Server

问题描述

请帮我解决以下问题.

我想从单个参数中获取多个值到 SQL Server 的过程中，我正在学习 SQL 和 C#.

I want to get multi values from a single parameter into the procedure in SQL Server, I'm learning SQL and C#.

这是我的代码:

ALTER PROCEDURE AProc_Getback 
    (@fcode VARCHAR(10))
AS
BEGIN
    UPDATE t_PALM_PersonnelFileMst 
    SET fdflag = 0, frdate = null, fddate = null,
        fdtype = null, fdreason = null
    WHERE fcode IN ('@fcode')

    DELETE FROM t_pald_dimissiontrm 
    WHERE fcode IN ('@fcode')
END

Exec AProc_Getback '512888,512889'

非常感谢

推荐答案

使用表值参数

CREATE TYPE FCodes AS TABLE ( 
    Code VARCHAR(32) -- Use type of "fcode" column
);

存储过程

ALTER PROCEDURE AProc_Getback 
(
    @fcode FCodes
)
AS
BEGIN
    UPDATE t_PALM_PersonnelFileMst SET
        fdflag = 0,
        frdate = null,
        fddate = null,
        fdtype = null, 
        fdreason = null
    WHERE fcode IN (SELECT Code FROM @fcode)

    DELETE FROM t_pald_dimissiontrm 
    WHERE fcode IN (SELECT Code FROM @fcode)
END

执行

DECLARE @Codes AS FCodes
INSERT INTO @Codes VALUES ('123'), ('456'), ('789')

EXEC AProc_Getback @Codes

从 C# 中，您可以创建 SqlDbType.Structure 类型的参数，并将 DataTable 作为值并将其传递给存储过程

From C# you can create parameter of type SqlDbType.Structure with DataTable as value and pass it to the stored procedures

var codesParameter = new SqlParameter
{
    ParameterName = "@fcodes",
    SqlDbType = SqlDbType.Structure,
    TypeName = "dbo.FCodes", // Important! - name of type in database
    Value = dataTableOfCodes // Should contain column with name "Code"
};

using (var connection = new SqlConnection(connectionString)) 
using (var command = connection.CreateCommand()) 
{
    command.CommandText = "dbo.AProc_Getback";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(codesParameter);

    connection.Open();
    command.ExecuteNonQuery();
}

这篇关于如何从单个参数中获取多个值到 SQL Server 中的过程中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！