关于存储过程

Question

SELECT C.PU_COUNM,C.PU_MINNM,C.PO_TITLE,C.DB_ISSN,C.CO_CODE,C.DN_YEAR,C.JINNIAN,C.MINGNIAN,C.DMINGNIAN,C.DDMINGNIAN,C.DDDMINGNIAN FROM  
( 
SELECT ISNULL(A.PU_COUNM,'계')AS PU_COUNM,A.PU_MINNM,A.PO_TITLE,A.DB_ISSN,A.CO_CODE,A.DN_YEAR, 
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011' THEN A.RE_CNT END),0) AS JINNIAN, 
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+1 THEN A.RE_CNT END),0) AS MINGNIAN, 
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+2 THEN A.RE_CNT END),0) AS DMINGNIAN, 
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+3 THEN A.RE_CNT END),0) AS DDMINGNIAN, 
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+4 THEN A.RE_CNT END),0) AS DDDMINGNIAN 
FROM booktory.idc_details AS A 
GROUP BY PU_COUNM,PU_MINNM,PO_TITLE,DB_ISSN,CO_CODE,DN_YEAR 
WITH ROLLUP 
)AS C 
WHERE (C.PU_COUNM <> '' AND C.PU_MINNM <> '' AND C.PO_TITLE <> '' AND C.DB_ISSN <> '' AND C.CO_CODE <> '' AND C.DN_YEAR <> '')OR (C.PU_COUNM = '계')我有这样一个语句 我想把 这整个语句当成参量 比如 @sql=整个语句
然后 EXEC fenye_cunchuguocheng  @sql 这样实现分页功能 分页功能是1页只显示15行 只查询15行 多排序
怎么写存储过程

Answer 1

楼主是想把分页的SQL都写完了再交给存储过程处理吗？
为什么要这样写？

Answer 2

create procedure fenye_cunchuguocheng 
as
begin
SELECT C.PU_COUNM,C.PU_MINNM,C.PO_TITLE,C.DB_ISSN,C.CO_CODE,C.DN_YEAR,C.JINNIAN,C.MINGNIAN,C.DMINGNIAN,C.DDMINGNIAN,C.DDDMINGNIAN FROM   
(  
SELECT ISNULL(A.PU_COUNM,'계')AS PU_COUNM,A.PU_MINNM,A.PO_TITLE,A.DB_ISSN,A.CO_CODE,A.DN_YEAR,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011' THEN A.RE_CNT END),0) AS JINNIAN,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+1 THEN A.RE_CNT END),0) AS MINGNIAN,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+2 THEN A.RE_CNT END),0) AS DMINGNIAN,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+3 THEN A.RE_CNT END),0) AS DDMINGNIAN,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+4 THEN A.RE_CNT END),0) AS DDDMINGNIAN  
FROM booktory.idc_details AS A  
GROUP BY PU_COUNM,PU_MINNM,PO_TITLE,DB_ISSN,CO_CODE,DN_YEAR  
WITH ROLLUP  
)AS C  
WHERE (C.PU_COUNM <> '' AND C.PU_MINNM <> '' AND C.PO_TITLE <> '' AND C.DB_ISSN <> '' AND C.CO_CODE <> '' AND C.DN_YEAR <> '')OR (C.PU_COUNM = '계')
end
go
EXEC fenye_cunchuguocheng

Answer 3

CREATE PROCEDURE [dbo].[PublicSplitPage_sp]
@TableName sysname,     --表名
@SqlStr varchar(2000)=null, --查询语句
@Condition varchar(8000), --查询条件
@PageIndex int, --页所引，从0开始
@PageSize int, --分页大小
@OrderDesc varchar(100), --倒排序字段，支持多个字段
@OrderAsc varchar(100), --顺排序字段，支持多个，字段先后顺序与倒排序一致
@PKField  varchar(50)='*', --索引字段
@SearField varchar(500), --查询字段
@RecordCount int out --返回记录数
AS
if @SqlStr is not null and @SqlStr<>''
begin
exec(@SqlStr)
select @RecordCount=@@ROWCOUNT
end 
else
begin
declare @iTop int,@tmpTop int
declare @vSQL nvarchar(2000)
set @vSQL = N'select @count=Count('+@PKField+') from ' + @TableName + ' where ' + @Condition
exec sp_ExecuteSQL @vSQL, N'@count int output', @RecordCount output
set @iTop=@PageSize
set @tmpTop=@RecordCount-(@PageSize*(@PageIndex-1))
if @RecordCount<@PageSize*@PageIndex
begin
set @iTop=@tmpTop
end

DECLARE @sqlStr1 varchar(1000)
IF @PageIndex = 1
SET @sqlStr1 = 'SELECT TOP ' + STR(@PageSize) + '  '+@SearField+'  FROM '+@TableName+' WHERE '+@Condition+' ORDER BY  '+@orderDESC
ELSE if @PageIndex<@RecordCount/(2*@PageSize) -- 此处可以加优化，从中间页往两边
SET @sqlStr1 = 'SELECT TOP ' + STR(@PageSize) + '  '+@SearField+' FROM (select top  ' + STR(@PageSize) + '  '+@SearField+' from (SELECT TOP ' 
     + STR(@PageSize*@PageIndex) + '  '+@SearField+'  FROM '+@TableName
     +' WHERE '+@Condition+'  ORDER BY '+@orderDESC+') TempTable order by '+@orderASC+') A  ORDER BY  '+@orderDESC
else
SET @sqlStr1 = 'SELECT TOP ' + STR(@iTop) + '  '+@SearField+' FROM (SELECT TOP ' 
     + STR(@tmpTop) + '  '+@SearField+'  FROM '+@TableName
     +' WHERE '+@Condition+'  ORDER BY '+@orderASC+') TempTable  ORDER BY  '+@orderDESC
EXEC(@sqlStr1)
end/*************************
注释：其中N字符用于防止
unicode字符乱码
**************************/不知道对你有没有用，这是个通用的分页存储过程

Answer 4

表中主键必须为标识列，[ID] int IDENTITY (1,1) 1.分页方案一：(利用Not In和SELECT TOP分页) 语句形式：  
SELECT TOP 页记录数量 * 
FROM 表名 
WHERE (ID NOT IN 
  (SELECT TOP (每页行数*(页数-1)) ID 
  FROM 表名 
  ORDER BY ID)) 
  ORDER BY ID 
//自己还可以加上一些查询条件 
例: 
select top 2 * 
from Sys_Material_Type 
where (MT_ID not in 
    (select top (2*(3-1)) MT_ID from Sys_Material_Type  order by MT_ID)) 
order by MT_ID 2.分页方案二：(利用ID大于多少和SELECT TOP分页） 语句形式： 
SELECT TOP 每页记录数量 * 
FROM 表名 
WHERE (ID > 
          (SELECT MAX(id) 
    FROM (SELECT TOP 每页行数*页数 id  FROM 表 
          ORDER BY id) AS T) 
      ) 
ORDER BY ID 例: 
SELECT TOP 2 * 
FROM Sys_Material_Type 
WHERE (MT_ID > 
          (SELECT MAX(MT_ID) 
          FROM (SELECT TOP (2*(3-1)) MT_ID 
                FROM Sys_Material_Type 
                ORDER BY MT_ID) AS T)) 
ORDER BY MT_ID 3.分页方案三：(利用SQL的游标存储过程分页) 
create  procedure SqlPager 
@sqlstr nvarchar(4000), --查询字符串 
@currentpage int, --第N页 
@pagesize int --每页行数 
as 
set nocount on 
declare @P1 int, --P1是游标的id 
@rowcount int 
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output 
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1 
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1 
set nocount off 4.总结: 
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。 
建议优化的时候，加上主键和索引，查询效率会提高。 通过SQL 查询分析器，显示比较：我的结论是: 
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句 
分页方案一：(利用Not In和SELECT TOP分页)  效率次之，需要拼接SQL语句 
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用 

Answer 5

--在存储过程里作为临时表的，未测试！
create proc get_re
(
@pagesize int,
@pagenum int
)
as
begin
SELECT C.PU_COUNM,C.PU_MINNM,C.PO_TITLE,C.DB_ISSN,C.CO_CODE,C.DN_YEAR,
C.JINNIAN,C.MINGNIAN,C.DMINGNIAN,C.DDMINGNIAN,C.DDDMINGNIAN,px = identity(int,1,1)
  into #temp
FROM   
(  
SELECT ISNULL(A.PU_COUNM,'계')AS PU_COUNM,A.PU_MINNM,A.PO_TITLE,A.DB_ISSN,A.CO_CODE,A.DN_YEAR,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011' THEN A.RE_CNT END),0) AS JINNIAN,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+1 THEN A.RE_CNT END),0) AS MINGNIAN,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+2 THEN A.RE_CNT END),0) AS DMINGNIAN,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+3 THEN A.RE_CNT END),0) AS DDMINGNIAN,  
ISNULL(SUM(CASE WHEN A.DN_YEAR = '2011'+4 THEN A.RE_CNT END),0) AS DDDMINGNIAN  
FROM booktory.idc_details AS A  
GROUP BY PU_COUNM,PU_MINNM,PO_TITLE,DB_ISSN,CO_CODE,DN_YEAR  
WITH ROLLUP  
)AS C  
WHERE (C.PU_COUNM <> '' AND C.PU_MINNM <> '' AND C.PO_TITLE <> '' 
AND C.DB_ISSN <> '' AND C.CO_CODE <> '' AND C.DN_YEAR <> '')OR (C.PU_COUNM = '계')select *
from #temp
where px between ((@pagenum - 1)*@pagesize+1) and @pagenum*@pagenum
end
go

Answer 6

CREATE PROCEDURE [dbo].[proc_Page1] 
@Table VARCHAR(1000), --表名 
@Primarykey VARCHAR(100), --主键 
@Condition VARCHAR(5000), --查询条件 
@PageNumber INT,   --开始页数 
@PageSize INT,   --每页大小 
@IsCount BIT   --是否获得记录数，0为否 
AS 
   DECLARE @SQL VARCHAR(8000) 
   IF @IsCount != 0 
begin
       SET @SQL = 'SELECT Count(*) FROM ' + @Table + ' WHERE ' + @Condition 
    end
   ELSE 
     BEGIN 
       IF @PageNumber = 1 
           SET @SQL = 'SELECT TOP ' + STR(@PageSize) + ' * FROM ' + @Table + ' WHERE ' + @Condition 
       ELSE 
         set @sql='select top '+str(@pageSize)
+' * From '+@Table+' where('+@Primarykey+'>(select max('+@Primarykey+') From (select top '
+str(@pageSize*(@PageNumber - 1))+' '+@Primarykey+' From '+@Table+' order by '+@Primarykey
+') as TempTable)) order by '+@Primarykey+''
  EXEC(@SQL)
END 已经测试过了 ，很好用也是很高效的分页存储过程

Answer 7

ALTER PROCEDURE [dbo].[Basic_Pagination2005]
@tblName      nvarchar(200),     --表名
@fidlelist    nvarchar(1000),   --要查询字段
@fldName      nvarchar(100),    --排序字段
@PageSize     int,              --页尺寸
@PageIndex    int,              --页码
@IsReCount    bit ,             -- 返回记录总数, 非 0 值则返回
@OrderType    bit,              -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar(1000)        --查询条件
AS
declare @sqlstr nvarchar(4000),
@tmpwhere nvarchar(4000),@tmporder nvarchar(100)
BEGIN
if @OrderType != 0
begin
set @tmporder = @fldName +' desc '
end
else
begin
        set @tmporder = @fldName +' asc '
end
set @tmpwhere='';
if(@strWhere!='')
begin
set @tmpwhere=' where '+@strWhere;
end
set @sqlstr=N'select * from
(select  '+@fidlelist+', ROW_NUMBER() OVER(orderby '+@tmporder+') as row from '+@tblName+@tmpwhere+') 
tmp where row between '+cast(((@PageIndex-1)*@PageSize+1) as nvarchar)+' and '+cast(@PageIndex*@PageSize as nvarchar);  
exec sp_executesql @sqlstr
if @IsReCount != 0
begin
set @sqlstr=N'select count(*) as Total from '+ @tblName+@tmpwhere
exec sp_executesql @sqlstr    
end
END