SQL Server Store Procedure 小记

最近开发工作告一段落,整理下用到的两个存储过程。

第一个是要删除大量的数据，此时需要重建index,加快数据搜索。

    CREATE PROCEDURE deleteExpiredRecord
    @Days nvarchar(5)
    AS
    BEGIN
    delete from tbl_order where datediff(day,create_datetime,getdate())>@days
    delete from tbl_ticket where datediff(day,create_datetime,getdate())>@days
    
    IF  EXISTS (SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID('tbl_order', N'U') and NAME='idx_orderRefID')
    BEGIN
    alter index idx_orderRefID on tbl_order rebuild;
    END
    ElSE IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID('tbl_order', N'U') and NAME='idx_orderRefID')
    BEGIN
    CREATE INDEX idx_orderRefID ON tbl_order (order_ref_id);
    END
    
    IF  EXISTS (SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID('tbl_ticket', N'U') and NAME='idx_ticketRefID')
    BEGIN
    alter index idx_ticketRefID on tbl_ticket rebuild;
    END
    ElSE IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID('tbl_ticket', N'U') and NAME='idx_ticketRefID')
    BEGIN
    CREATE INDEX idx_ticketRefID ON tbl_ticket (order_ref_id);
    END
    
END

第二个场景是我们拿到多条reference number对应的status, 我们需要通过reference number去update对应的status, 通过循环一次处理完，而不是多次访问DB Table.

这个时候就需要用到DataType 往Procedure中传值，DataType可以接受多个不通类型的值，完全支持自定义。

我们先声明DataType和需要用到这个DataType的Procedure.

    CREATE TYPE dbo.TransDT
    AS TABLE
    (
      order_ref VARCHAR(50), status VARCHAR(10)
    );
    GO
     
    CREATE PROCEDURE updateTransaction
    @DT transDT READONLY
    AS
    BEGIN
            BEGIN
             UPDATE e SET e.payment_status=d.status,e.last_update_datetime=GETDATE() FROM tbl_order e, @DT d 
                WHERE e.order_ref_ID=d.order_ref and e.payment_status='0'
            END
    END

然后我们看下在代码逻辑中如何用它:

1.申明DataTable类型,并将接口返回值递归出给它。

    CREATE TYPE dbo.TransDT
    AS TABLE
    (
      order_ref VARCHAR(50), status VARCHAR(10)
    );
    GO
     
    CREATE PROCEDURE updateTransaction
    @DT transDT READONLY
    AS
    BEGIN
            BEGIN
             UPDATE e SET e.payment_status=d.status,e.last_update_datetime=GETDATE() FROM tbl_order e, @DT d 
                WHERE e.order_ref_ID=d.order_ref and e.payment_status='0'
            END
    END

我们看下如何用赋好值的dt.

tmpResult = await _settlementReportDao.UpdateOrderStatus(dt);

在UpdateOrderStatus中去调用执行SP的方法

await sqlHelper.ExecuteNonQueryStroreprocedureWithValue(_spName, dt);

其中看下Store Procedure中如何传入dt

public async Task<int> ExecuteNonQueryStroreprocedureWithValue(string storeProcedureName, DataTable dt)
        {
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = this.commandTimeout;

            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                connection.Open();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = storeProcedureName;
                command.Parameters.AddWithValue("@DT", dt);
                return await command.ExecuteNonQueryAsync();
            }
            
        }