SQL Server表值参数使用

原创已于 2023-09-16 15:21:13 修改 · 8.5k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#java #开发语言

于 2018-04-06 17:47:44 首次发布

C# 同时被 2 个专栏收录

41 篇文章

订阅专栏

SQL与数据库资料

20 篇文章

订阅专栏

本文介绍如何在SQL Server中使用表值参数，包括创建表值类型、存储过程及调用示例。从定义表值参数到实际应用，涵盖了C#调用存储过程的方法。

SQL Server自2008版本开始支持表类型的参数，可以用于存储过程、函数等，也可以直接使用C#等语言调用SQL直接查询。

首先创建一个表值类型：

设置表格参数（后面变化时不可直接修改，需要删除重建）：

USE [test]
GO

-- Create the data type
CREATE TYPE dbo.type_testStruct AS TABLE 
(
	ID INT NOT NULL,
	Val DECIMAL NOT NULL, 
    PRIMARY KEY (ID)
)
GO

弄个存储过程：表值参数只能是Readonly的，不能作为output

CREATE PROC structTest
    (
        @table AS type_testStruct READONLY
    )
AS
    BEGIN

        SELECT ID ,
               Val * 2 AS Val
        FROM   @table;
    END;

测试调用存储过程：

DECLARE @table AS type_testStruct;
INSERT @table ( ID , Val ) VALUES ( 1 , 10 )
INSERT @table ( ID , Val ) VALUES ( 2 , 20 )
INSERT @table ( ID , Val ) VALUES ( 3 , 30 )

EXEC structTest @table

结果：

C#调用示例：（此示例调用另外一个过程，用法相同）

        DataTable dt = new DataTable();
        dt.Columns.Add("ID", typeof(int));
        dt.Columns.Add("Val", typeof(decimal));
        DataRow row = dt.NewRow();
        row[0] = 9;
        row[1] = 1000;
        dt.Rows.Add(row);

        DataRow row2 = dt.NewRow();
        row2[0] = 10;
        row2[1] = 2000;
        dt.Rows.Add(row2);

        var sql = "getInfoForReportByMOProcessRowIDAndQty";
        var dt2 = SqlHelper.ExecuteDataset(db.ConnectionStrings.test, CommandType.StoredProcedure, sql, new SqlParameter("values", dt)).Tables[0];

        JSONHelper.WriteJSON(dt2);

结果：

可以看到，调用时和普通参数没有太大差别，只是传的值是一个DataTable而已。

直接SQL调用，主要差别在参数要设置类型：

                    SqlParameter tablePara = new SqlParameter("values", SqlDbType.Structured);//参数的类型，SqlDbtype.Structured
                    tablePara.TypeName = "dbo.IDValueStruct"; //这个就是建立的类型名称，比如上面的type_testStruct
                    tablePara.Value = valuesTable;

其他就和普通的查询一样了

另外注意一点，C#中调用时传递的DataTable参数，要求列的顺序要和类型中表列顺序一致，列名无要求，可以和类型中的字段名称不一致。