MSSQL 表值参数的使用

发布时间：2023-05-08 12:55:45 所属栏目：MsSql教程来源：

导读：use TestDb;
/* 创建一个自定义类型 */
go
create type T_ID as Table (
Id bigint not null ,
[State] int not null,
)
go
/* 创建存储过程 */
CREATE PROCEDURE SP_CountProduct
@prod

use TestDb;
/* 创建一个自定义类型 */
go
create type T_ID as Table (
   Id bigint not null ,
[State] int not null,
)
go
/* 创建存储过程 */
CREATE PROCEDURE SP_CountProduct
   @product dbo.T_ID readonly,
   @createBy bigint ,
   @createName nvarchar(50)
AS
BEGIN

   if not exists (select p.* from Products p inner join @product p1 on p1.Id = p.Id)
   begin
       return -1;
   end
   declare @cnt int;
select @cnt = COUNT(*) from from Products p
inner join @product p1 on p1.Id = p.Id and p.[State] != p1.[State];
return @cnt;
END
go
/*授权自定义类型的执行权限 */
grant exec on TYPE::Tetst.dbo.T_ID to dbuser
go
/*授权存储过程的执行权限 */
grant exec on Tetst.dbo.CountProduct to dbuser
代码执行
//using Dapper
using (var dt = new DataTable())
{
dt.Columns.Add("Id", typeof(long));
foreach (var id in dto.Ids)
{
dt.Rows.Add(id);
}
var parameters = new DbParameter[]
{
// sqlDbType :sqlDbType.Structured, TypeName : 我们创建的自定义类型
new sqlParameter("@product", sqlDbType.Structured) {TypeName = "T_ID", Value = dt, }
new sqlParameter {ParameterName = "@createBy", Value = session.UserId, DbType = DbType.Int64,},
new sqlParameter {ParameterName = "@createName", Value = session.UserName ?? string.Empty, Size = 50, DbType = DbType.String}
};
var r = await _dbConnection.ExecuteNonQueryAsync("SP_CountProduct", CommandType.StoredProcedure, parameters);
}

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!