sql的一些操作，游标分页，事务

----sql事务---

CREATE PROC USP_PROCNAME
//参数
AS
 BEGIN TRY
   BEGIN TRANSACTION
    //SQL语句
 COMMIT
END TRY
begin catch
IF(@@TRANCOUNT>0)
ROLLBACK
DECLARE @ErrMsg nvarchar(4000),@ErrSeverity int
SELECT @ErrMsg=ERROR_MESSAGE(),@ErrSeverity=ERROR_SEVERITY()
RAISERROR(@ErrMsg,@ErrSeverity,1)
end catch

 

 

----sql分页（微软）---

/// <summary>
/// 得到列表：分页
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示的记录条数</param>
/// <param name="pageCount">总共有多少页</param>
/// <param name="recordCount">记录总数</param>
/// <returns></returns>
public DataSet GetPageSplit(string sql, int pageIndex, int pageSize, out int pageCount, out int recordCount)
{
            if (pageIndex <= 0)
            {
                pageIndex = 1;
            }

            DataSet ds = new DataSet();

            SqlParameter[] parameters = new SqlParameter[]{
                               DbTShop.MakeInParam("@sql", SqlDbType.VarChar, -1, sql),
                               DbTShop.MakeInParam("@pageIndex", SqlDbType.Int, 4, pageIndex),
                               DbTShop.MakeInParam("@pageSize", SqlDbType.Int, 4, pageSize),
                               DbTShop.MakeOutParam("@pageCount", SqlDbType.Int, 4),
                               DbTShop.MakeOutParam("@recordCount", SqlDbType.Int, 4)
            };

            ds = DbTShop.ExecuteDataset("PageSplit", parameters);

            pageCount = parameters[3].Value != null ? int.Parse(parameters[3].Value.ToString()) : 0;
            recordCount = parameters[4].Value != null ? int.Parse(parameters[4].Value.ToString()) : 0;

}

-- =============================================
-- 分页存储过程
-- =============================================

ALTER PROCEDURE [dbo].[PageSplit]
    @sql VARCHAR(MAX) ,--要执行的sql语句
    @pageIndex INT = 1 , --当前页码
    @pageSize INT ,--每页的大小
    @pageCount INT = 0 OUT ,--总页数
    @recordCount INT = 0 OUT--总记录数
AS
    SET NOCOUNT ON
    DECLARE @p1 INT
    EXECUTE sp_cursoropen @p1 OUTPUT, @sql, @scrollopt = 1, @ccopt = 1,
        @rowcount = @pageCount OUTPUT
    SET @recordCount = @pageCount
    SELECT  @pageCount = CEILING(1.0 * @pageCount / @pageSize)
    IF @pageIndex > @pageCount
        SELECT  @pageIndex = @pageCount
    SELECT  @pageIndex = ( @pageIndex - 1 ) * @pageSize + 1

    EXECUTE sp_cursorfetch @p1, 16, @pageIndex, @pageSize
    EXECUTE sp_cursorclose @p1