Sqlserver分页存储过程

• 异常信息
  //关于存储过程传出值"String[2]: Size 属性具有无效大小值 0,” 错误  )

  //传出参数必须指定大小

存储过程或函数 'proc_GetEmployee' 需要参数 '@CustomerID'，但未提供该参数
忘记设置：command.CommandType = CommandType.StoredProcedure;

 

• 存储过程
  --传入当前页和每页显示行数

  --declare @startRow int, @endRow int
  --set @startRow = (@pageIndex - 1) * @pageSize +1
  --set @endRow = @startRow + @pageSize –1

IF(OBJECT_ID('proc_Pager','P') IS NOT NULL)
   DROP PROC proc_Pager
GO 
CREATE PROC proc_Pager
(
 @tabName VARCHAR(30),
 @pkName VARCHAR(10),
 @startIndex INT,
 @endIndex INT,
 @totalCount VARCHAR(10) OUT
)
AS
 DECLARE @c VARCHAR(500)

  --SET @c='SELECT '+@totalCount+'=count(*) FROM '+@tabName
  SET @c='SELECT count(*) FROM '+ @tabName
  EXEC(@c)
  
  SET @c=' SELECT * FROM 
    (SELECT ROW_NUMBER() OVER(ORDER BY '+@pkName+') as rowId, * FROM '+@tabName+') temp 
   WHERE temp.rowId between '+CAST(@startIndex AS VARCHAR(5))+' AND '+CAST(@endIndex AS VARCHAR(5)) 
   
   EXEC(@c)
GO

• 调用存储
  
  DECLARE @totalCount INT --SET @totalCount=0 EXEC proc_Pager 'Customers','CustomerID',11,20,@totalCount OUT PRINT '总行数'+CAST(@totalCount AS VARCHAR(5))
• VS调用存储过程
  
  DbHelperSqlServer db = new DbHelperSqlServer(); ParamsHelperSqlServer paras = new ParamsHelperSqlServer(); paras.Add("@tabName", "Customers"); paras.Add("@pkName", "CustomerID"); paras.Add("@startIndex", 11); paras.Add("@endIndex", 20); //关于存储过程传出值"String[2]: Size 属性具有无效大小值 0,” 错误 ) //传出参数必须指定大小 paras.Add("@totalCount", string.Empty, System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Output); var v = db.GetDataSet("proc_Pager", paras.ListParameter, CommandType.StoredProcedure);