存储过程

提前这个存储过程，真的是让我心里很是委屈，做开发这么久了，我竟然不会存储过程，纠结了我两天，下面就来看看存储过程

存储过程就是一种特殊的函数，基本上没有返回值，如果要有返回值，则必须是0,1

存储过程是存放在服务器端的，一旦存储过程建立之后，则以后就可以随时使用，不在进行编译了

创建存储过程

Create Proc Pro_Table //创建一个存储过程

(@UserId Int) // 定义参数

Begin

Declare @Sql Navrchar(2000); // 声明一个变量

set @Sql='select * from car';

Exec(@Sql) //执行存储过程

Print(@Sql) //打印

End

带分页的存储过程，这个这么我好久了

Alter proc  Pro_CarComment
(
@UserId int,
@PageSize int,                     --每页多少条记录
@PageIndex int = 1 ,               --指定当前为第几页
@TotalRecord int output,            --返回总记录数
@TotalPageCount int output         --返回总页数
)
as
Begin

Declare @StartRecord int;
Declare @EndRecord int;
Declare @TotalCountSql nvarchar(500);
Declare @SqlString nvarchar(2000);
set @StartRecord =(@PageIndex-1)*@PageSize+1
set @EndRecord =@StartRecord +@PageSize-1
set @TotalCountSql='select count(*) from (select cm.id,cm.Content,cm.CarId ,cm.CreateTime from
(SELECT   UserProfileId,[carid],max(id) as id
FROM [BusinessData].[dbo].[CarComment]
group by [carid],  UserProfileId) as temp ,[CarComment] as cm
where  temp.CarId=cm.CarId  and temp.id=cm.id)temp2,car as c
where temp2.CarId=c.Id and c.UserId='+CAST( @UserId as varchar)
set @SqlString='select,row_number() over (order by desc) as rowId, c.id as cid,c.carname,temp2.Id as cmid,temp2.content,temp2.CreateTime as cmtime from
(select cm.id,cm.Content,cm.CarId ,cm.CreateTime from
(SELECT   UserProfileId,[carid],max(id) as id
FROM [BusinessData].[dbo].[CarComment]
group by [carid],  UserProfileId) as temp ,[CarComment] as cm
where  temp.CarId=cm.CarId  and temp.id=cm.id)temp2,car as c
where temp2.CarId=c.Id and c.UserId='+CAST( @UserId as varchar)

EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数

set @TotalPageCount=(@TotalRecord+@PageSize-1)/@PageSize
set @SqlString ='select * from ('+@SqlString+')as t where rowId between '+ltrim(str(@StartRecord))+' and '+ ltrim(str(@EndRecord));

Exec(@SqlString)    
    print @SqlString
    print @TotalRecord
    print @TotalPageCount
    end
    Go

下面在记一个通用的分页存储过程

ALTER(Create) procedure [dbo].[p_fen_ye] @table varchar(20),@id varchar(20),@orderby int,@pagesize int,@pageno int,@iswhere int,@where varchar(500)
as
declare @by varchar(10)
set @by='asc'
if(@orderby=1)
begin
set @by='desc';
end
declare @start varchar(10);
set @start = cast(((@pageno-1)*@pagesize) as varchar);
declare @sql varchar(500);
if(@iswhere!=0)
begin
set @sql='select top '+cast(@pagesize as varchar)+' * from '+@table+' where '+@id+' not in (select top '+@start+' '+@id+' from '+@table+' where '+@where+' order by '+@id+' '+@by+') and '+@where+' order by '+@id+' '+@by;
end
else
begin
set @sql='select top '+cast(@pagesize as varchar)+' * from '+@table+' where '+@id+' not in (select top '+@start+' '+@id+' from '+@table+' order by '+@id+' '+@by+') order by '+@id+' '+@by;
end
execute (@sql)

这个还有一个朋友给我的通用的分页存储过程，只需要传2个参数

CREATE   PROCEDURE Pro_CarComment
@page_size int ,--页面大小
@current_page int  output-- 当前第几页
as 
begin 
     declare @page_count int--页面总数
     declare @last_page  int--尾页
     declare @first_page   int--首页
     declare @sql nvachar(2000) 
    select @page_count =  COUNT(*) from Employee 
     if( @page_count %@page_size=0)
        begin
          set @last_page = @page_count / @page_size
         end
      if (@page_count %@page_size !=0)
         begin
         set @last_page = @page_count/@page_size +1
          end
     if ( @current_page <=0)
      begin 
          select  @first_page=1, @current_page = @first_page
         end 
      if (@current_page >= @last_page)
        begin
          set @current_page= @last_page
         end 
    select  top(@page_size) *
   from Employee where Employee_no not in ( select  top(@page_size * (@current_page-1)) Employee_no from  Employee )
end

依个人习惯创建分页存储过程，再追加一个吧，目前简单的存储过程会创建了

-创建分页存储过程
-------------------------------------------------------------------
CREATE PROC [dbo].[sp_PageView]
    @tbname SYSNAME ,               --要分页显示的表名   
    @FieldKey NVARCHAR(1000) ,      --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段   
    @PageCurrent INT = 1 ,               --要显示的页码   
    @PageSize INT = 10 ,                --每页的大小(记录数)   
    @FieldShow NVARCHAR(1000) = '' ,      --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段   
    @FieldOrder NVARCHAR(1000) = '' ,      --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC   用于指定排序顺序   
    @Where NVARCHAR(1000) = '' ,     --查询条件   
    @RecordCount INT OUTPUT             --总页数   
AS 
    DECLARE @PageCount AS INT
    SET NOCOUNT ON   
--检查对象是否有效   
    IF OBJECT_ID(@tbname) IS NULL 
        BEGIN   
            RAISERROR(N'对象"%s"不存在',1,16,@tbname)   
            RETURN   
        END   
    IF OBJECTPROPERTY(OBJECT_ID(@tbname), N'IsTable') = 0
        AND OBJECTPROPERTY(OBJECT_ID(@tbname), N'IsView') = 0
        AND OBJECTPROPERTY(OBJECT_ID(@tbname), N'IsTableFunction') = 0 
        BEGIN   
            RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)   
            RETURN   
        END   
   
--分页字段检查   
    IF ISNULL(@FieldKey, N'') = '' 
        BEGIN   
            RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)   
            RETURN   
        END   
   
--其他参数检查及规范   
    IF ISNULL(@PageCurrent, 0) < 1 
        SET @PageCurrent = 1   
    IF ISNULL(@PageSize, 0) < 1 
        SET @PageSize = 10   
    IF ISNULL(@FieldShow, N'') = N'' 
        SET @FieldShow = N'*'   
    IF ISNULL(@FieldOrder, N'') = N'' 
        SET @FieldOrder = N''   
    ELSE 
        SET @FieldOrder = N'ORDER BY ' + LTRIM(@FieldOrder)   
    IF ISNULL(@Where, N'') = N'' 
        SET @Where = N''   
    ELSE 
        SET @Where = N'WHERE (' + @Where + N')'   
   
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)   
    IF @PageCount IS NULL 
        BEGIN   
            DECLARE @sql NVARCHAR(4000)   
            SET @sql = N'SELECT @RecordCount=COUNT(*)' + N' FROM ' + @tbname
                + N' ' + @Where   
            EXEC sp_executesql 
                @sql ,
                N'@RecordCount int OUTPUT' ,
                @RecordCount OUTPUT   

            SET @PageCount = ( @RecordCount + @PageSize - 1 ) / @PageSize   
        END   
   
--计算分页显示的TOPN值   
    DECLARE
        @TopN VARCHAR(20) ,
        @TopN1 VARCHAR(20)   
    SELECT
        @TopN = @PageSize ,
        @TopN1 = ( @PageCurrent - 1 ) * @PageSize   
   
--第一页直接显示   
    IF @PageCurrent = 1 
        EXEC(N'SELECT TOP '+@TopN   
        +N' '+@FieldShow   
        +N' FROM '+@tbname   
        +N' '+@Where   
        +N' '+@FieldOrder)   
    ELSE 
        BEGIN   
    --处理别名   
            IF @FieldShow = N'*' 
                SET @FieldShow = N'a.*'   
   
    --生成主键(惟一键)处理条件   
            DECLARE
                @Where1 NVARCHAR(4000) ,
                @Where2 NVARCHAR(4000) ,
                @s NVARCHAR(1000) ,
                @Field SYSNAME   
            SELECT
                @Where1 = N'' ,
                @Where2 = N'' ,
                @s = @FieldKey   
            WHILE CHARINDEX(N',', @s) > 0 
                SELECT
                    @Field = LEFT(@s, CHARINDEX(N',', @s) - 1) ,
                    @s = STUFF(@s, 1, CHARINDEX(N',', @s), N'') ,
                    @Where1 = @Where1 + N' AND a.' + @Field + N'=b.' + @Field ,
                    @Where2 = @Where2 + N' AND b.' + @Field + N' IS NULL' ,
                    @Where = REPLACE(@Where, @Field, N'a.' + @Field) ,
                    @FieldOrder = REPLACE(@FieldOrder, @Field, N'a.' + @Field) ,
                    @FieldShow = REPLACE(@FieldShow, @Field, N'a.' + @Field)   
            SELECT
     --@Where=REPLACE(@Where,@s,N'a.'+@s),   
                @FieldOrder = REPLACE(@FieldOrder, @s, N'a.' + @s) ,
                @FieldShow = REPLACE(@FieldShow, @s, N'a.' + @s) ,
                @Where1 = STUFF(@Where1 + N' AND a.' + @s + N'=b.' + @s, 1, 5,
                                N'') ,
                @Where2 = CASE WHEN @Where = '' THEN N'WHERE ('
                               ELSE @Where + N' AND ('
                          END + N'b.' + @s + N' IS NULL' + @Where2 + N')'   
    --执行查询 
  
            EXEC(N'SELECT TOP '+@TopN   
            +N' '+@FieldShow   
            +N' FROM '+@tbname   
            +N' a LEFT JOIN(SELECT TOP '+@TopN1   
            +N' '+@FieldKey   
            +N' FROM '+@tbname   
            +N' a '+@Where   
            +N' '+@FieldOrder   
            +N')b ON '+@Where1   
            +N' '+@Where2   
            +N' '+@FieldOrder)   
        END

存储过程

推荐阅读