SQL 储存过程 改ASPNETPAGE 分页自动生成储存过程 有小问题.求教
USE [ND]
GO
/****** Object: StoredProcedure [dbo].[sp_GetProducts] Script Date: 08/15/2013 13:28:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetPagerEntities]
(@Designer_ID Int,
@PageSize int,
@PageIndex int,
@TableName nvarchar(64), //自定义表名.想弄个通用 的分页储存
@OrderBy nvarchar(64),
@docount bit)
as
if(@docount=1)
exec ('select count(*) from'+ @TableName) //查看论坛别的贴子 然后修改此处了.
else
begin
with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY product_name+@OrderBy)AS Row, * from @TableName where designer_id =+@Designer_ID ) //问题在这里的@TableName 不知道怎么改..
SELECT * FROM temptbl where Row between (@PageIndex-1)*@PageSize+1 and (@PageIndex-1)*@PageSize+@PageSize
end
必须声明表变量 "@TableName"。
小弟分不多...先感谢各位的帮助. SQL 分页 C#
[解决办法]
--表名不能当变量执行。要么拼SQL，用EXEC执行；要么拼SQL，并参数化查询。建议后者：
CREATE PROCEDURE [dbo].[sp_GetPagerEntities]
@Designer_ID Int,
@PageSize int,
@PageIndex int,
@TableName nvarchar(64),
@OrderBy nvarchar(64),
@docount bit
as
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
if(@docount=1)
exec ('select count(*) from '+ @TableName)
else
BEGIN
SET @sql = N'
;with temptbl as
(
SELECT ROW_NUMBER() OVER (ORDER BY product_name {1}) AS Row, *
from {2}
where designer_id = @Designer_ID
)
SELECT * FROM temptbl
where Row between (@PageIndex-1)*@PageSize+1 and (@PageIndex-1)*@PageSize+@PageSize
'
SET @sql = REPLACE(@sql, '{1}', @OrderBy)
SET @sql = REPLACE(@sql, '{2}', @TableName)
--PRINT @sql
EXEC sp_executesql @sql, N'@Designer_ID int, @PageIndex int, @PageSize int', @Designer_ID, @PageIndex, @PageSize
end
GO
--测试:参数改成你自己的
EXEC [sp_GetPagerEntities] 1, 10, 1, 'master..spt_values', ',number', 0
