Sql 存储过程 小知识

　　公司存储过程用得比较多，现在总结下所学到的一些东西。

　　1、最简单的存储过程

　　需求：查询某个表的所有内容，并返回记录数

create proc testProc
(
    @RecordCount int output --记录数量
)
as 
begin
    select * from NormalEnterprise;
    select @RecordCount=COUNT(1) from NormalEnterprise;
end

 

　　2、稍微复杂点的存储过程

　　需求：动态查询某个表的内容，并返回记录数

ALTER proc [dbo].[testProc]
(
    @TableName nvarchar(50) ,--表明
    @RecordCount int output --记录数量
)
as 
begin
    Declare @strsql nvarchar(max)
    set @strsql='select * from '+QUOTENAME(@TableName);
    select @RecordCount=COUNT(1) from NormalEnterprise;
    
    exec sp_executesql @strsql;
end

　QUOTENAME 函数可以帮助我们把字串成为有效的标识符，使之符合Sql Server的规则。

 

　3、简单的分页查询语句

　　需求：对某个表数据分页查询

ALTER proc [dbo].[testProc]
(
    @PageIndex int,--第几页
    @PageSize int,--页大小
    @RecordCount int output --记录数量
)
as 
begin
    Declare @strsql nvarchar(max)
    Set @strsql='
    Declare @temp table
    (
        ID varchar(50),
        Name varchar(50),
        rowIndex int --自定义行号
    )
    Insert into @temp
    select ID,
    Name,
    ROW_NUMBER() over(order by ID)
    from NormalEmployee
    
    select * from @temp where rowIndex between (@PageSize*(@PageIndex-1)+1) And @PageSize*@PageIndex
    select @RecordCount=count(1) from @temp
    '
    exec sp_executesql @strsql,N'
        @PageIndex int,--第几页
        @PageSize int,--页大小
        @RecordCount int output --记录数量
    ',
    @PageIndex,
    @PageSize,
    @RecordCount
end

　

　4、根据某个查询条件获得分页数据

ALTER proc [dbo].[testProc]
(
    @Name varchar(50)=null, --查询参数
    @PageIndex int,--第几页
    @PageSize int,--页大小
    @RecordCount int output --记录数量
)
as 
begin
    Declare @strsql nvarchar(max)
    Set @strsql='
    Declare @temp table
    (
        ID varchar(50),
        Name varchar(50),
        rowIndex int --自定义行号
    )
    Insert into @temp
    select ID,
    Name,
    ROW_NUMBER() over(order by ID)
    from NormalEmployee
    where 1=1 '
    +case when @Name is null then '' else ' and Name like ''%''+@Name+''%'' '  end 
    +'
    select * from @temp where rowIndex between (@PageSize*(@PageIndex-1)+1) And @PageSize*@PageIndex
    select @RecordCount=count(1) from @temp
    '
    exec sp_executesql @strsql,N'
        @Name varchar(50)=null,
        @PageIndex int,--第几页
        @PageSize int,--页大小
        @RecordCount int output --记录数量
    ',
    @Name,
    @PageIndex,
    @PageSize,
    @RecordCount
end

　注意：

　　　　1、这符号 '' 是成对出现的，写的时候先打上一对，在写其他代码，不容易写错。

 　　　 2、在写模糊查询时，注意写法 ''%''+查询参数名。

　　　  3、Declare @temp table 申明了一个临时表，我们把数据放入这个表中，再从临时表查询出来。

 

　　在C#中如何调用存储过程呢

　 parameter.Add(new DBParameter("PageIndex", pageIndex));
   parameter.Add(new DBParameter("PageSize", pageSize));

   DBParameter sp = new DBParameter("PageCount");
   sp.Direction = ParameterDirection.Output;
   sp.DbType = DbType.Int32;
   sp.Value = 0;
   parameter.Add(sp);

   DataSet ds = dal.ExecuteDataSet(obj, parameter, CommandType.StoredProcedure);
   pageCount = int.Parse(sp.Value.ToString());