sqlserver存储过程

工作这么久了，但不会存储过程，决定抽时间学习下.

例子一 新建存储过程
    CREATE PROCEDURE hello
    as
    select top 2 * from  student;
    新建立一个名为hello的存储过程
    调用: hello
例子二 修改存储过程
  修改存储过程,设置需要传入参数xid
  ALTER PROCEDURE  hello   @xid  int         // 声明要传入int类型的参数，形参名为xid
  as

  select top 2 * from  student where id = @xid
  调用: hello 123
          hello @xid = 123

 例三 带输出参数的存储过程
   ALTER PROCEDURE hello
        @idx int,
        @count int output
   AS
      select @count=count(*)  from TableA where id = @idx

调用:

   declare @countxx int
   exec hello 61082,@countxx output
   print @countxx

  例四 分页存储过程
CREATE PROCEDURE [dbo].[sp_demo] @ii_PageIndex INT ,--数据页码 @ii_PageSize INT ,--每页显示数据条数 @oi_RecordCount INT OUT ,--数据总条数 @oi_PageCount INT OUT ,--数据总页数 @ii_QueryColumns nvarchar(4000),-- 查询字段 @in_Sqlwhere nvarchar(4000) ,--查询条件 @ii_Flag INT --是否默认查询标识 AS BACKUP LOG Demo WITH NO_LOG DUMP TRANSACTION tempdb WITH NO_LOG DUMP TRANSACTION Demo WITH NO_LOG DECLARE @vn_SQLSTR NVARCHAR(4000) DECLARE @vd_newestdt datetime declare @vn_strSQL nvarchar(4000) if @ii_QueryColumns = null or @ii_QueryColumns = '' SET @ii_QueryColumns=N'*' if @ii_Flag=0 BEGIN SELECT @vd_newestdt = max(systime) FROM [tb_dis_carrier] SET @in_Sqlwhere = N' systime='''+convert(varchar(30),@vd_newestdt,121)+'''' END --确定当前总记录条数 set @vn_strSQL='SELECT @oi_RecordCount =COUNT(*) FROM [tb_demo] where '+@in_Sqlwhere exec sp_executesql @vn_strSQL, N'@oi_RecordCount int=0 OUTPUT', @oi_RecordCount=@oi_RecordCount OUTPUT --确定分页数 SET @oi_PageCount = CEILING ( @oi_RecordCount * 1.0 / @ii_PageSize ) --确定每页记录条数 --第一页 IF @ii_PageIndex = 0 OR @oi_PageCount <= 1 SET @vn_SQLSTR =N' SELECT TOP '+STR( @ii_PageSize )+ @ii_QueryColumns +' from tb_demo where '+@in_Sqlwhere+ ' order by systime desc' --最后一页 ELSE IF @ii_PageIndex = @oi_PageCount - 1 SET @vn_SQLSTR =N' SELECT '+@ii_QueryColumns+' FROM tb_demo where '+@in_Sqlwhere+ ' and id not in (select top '+STR(@ii_PageSize*@ii_PageIndex)+ ' id from tb_demo where '+@in_Sqlwhere+') ' + ' order by systime desc' --中间页 ELSE SET @vn_SQLSTR =N' SELECT top '+STR( @ii_PageSize )+@ii_QueryColumns+ ' FROM ( SELECT TOP '+STR( @ii_PageSize * (@ii_PageIndex+1) )+'id,'+@ii_QueryColumns+ ' from tb_demo where '+@in_Sqlwhere+' ) TempTable where id not in (select top '+ STR(@ii_PageSize*@ii_PageIndex)+' id from tb_demo where '+@in_Sqlwhere+' ) '+ ' order by systime desc' EXEC (@vn_SQLSTR)
create PROCEDURE [dbo].[sp_dis_demo2] @ii_PageIndex INT ,--数据页码 @ii_PageSize INT ,--每页显示数据条数 @oi_RecordCount INT OUT ,--数据总条数 @oi_PageCount INT OUT ,--数据总页数 @ii_QueryColumns nvarchar(4000),-- 查询字段 @in_Sqlwhere nvarchar(4000) ,--查询条件 @ii_Flag INT --是否默认查询标识 AS DUMP TRANSACTION Demo WITH NO_LOG DECLARE @vn_SQLSTR NVARCHAR(4000) DECLARE @vi_newestyear int DECLARE @vi_newestmonth int DECLARE @vi_newestday int DECLARE @vd_newestdt datetime declare @vn_strSQL nvarchar(4000) if @ii_QueryColumns = null or @ii_QueryColumns = '' SET @ii_QueryColumns=N'*' if @ii_Flag=0 BEGIN SELECT @vd_newestdt = max(sysdatetime) FROM [demo] SET @in_Sqlwhere = N'sysdatetime='''+convert(varchar(30),@vd_newestdt,121)+'''' END --确定当前总记录条数 set @vn_strSQL='SELECT @oi_RecordCount =COUNT(*) FROM [demo] where '+@in_Sqlwhere exec sp_executesql @vn_strSQL, N'@oi_RecordCount int=0 OUTPUT', @oi_RecordCount=@oi_RecordCount OUTPUT --确定分页数 SET @oi_PageCount = CEILING ( @oi_RecordCount * 1.0 / @ii_PageSize ) SET @vn_SQLSTR =N' WITH QueryTable AS ('+ ' SELECT ROW_NUMBER() OVER (ORDER BY sysdatetime desc) AS RowIndex,'+@ii_QueryColumns+ ' FROM demo with(nolock) where '+@in_Sqlwhere+')'+ ' SELECT '+@ii_QueryColumns+' FROM QueryTable'+ ' WHERE RowIndex BETWEEN '+str(@ii_PageSize*@ii_PageIndex)+' AND '+str(@ii_PageSize * (@ii_PageIndex+1)) print @vn_SQLSTR EXEC (@vn_SQLSTR) //另一种写法 SET @vn_SQLSTR = N' Select '+@ii_QueryColumns+ ' from '+ ' (SELECT ROW_NUMBER() OVER (ORDER BY sysdatetime desc) AS RowIndex,'+@ii_QueryColumns+ ' FROM tb_dis_gprs with(nolock) where '+@in_Sqlwhere+' ) as QueryTable'+ ' WHERE RowIndex BETWEEN '+str(@ii_PageSize*@ii_PageIndex)+' AND '+str(@ii_PageSize * (@ii_PageIndex+1))
//Ps.访问sqlserver存储过程的C#代码示例 public DataTable GetDataTable(string procName, int pageIndex, int pageSize, ref int recordCount,ref int lvpageCount, string queryColumns, string sqlwhere, int flag){ DataSet ds = new DataSet(); DataTable dt = new DataTable(); List<SqlParameter> sqlParamList = new List<SqlParameter>{ new SqlParameter("@ii_PageIndex",SqlDbType.Int), new SqlParameter("@ii_PageSize",SqlDbType.Int), new SqlParameter("@oi_RecordCount",SqlDbType.Int), new SqlParameter("@oi_PageCount",SqlDbType.Int), new SqlParameter("@in_Sqlwhere",SqlDbType.VarChar), new SqlParameter("@ii_Flag",SqlDbType.Int) }; SqlParameter[] sqlParam = sqlParamList.ToArray<SqlParameter>(); sqlParam[0].Value = pageIndex; sqlParam[1].Value = pageSize; sqlParam[2].Direction = ParameterDirection.Output; sqlParam[3].Direction = ParameterDirection.Output; sqlParam[4].Value = sqlwhere; sqlParam[5].Value = flag; using (ds = SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, procName, sqlParam)){ dt = ds.Tables[0]; } recordCount = int.Parse(sqlParam[2].Value.ToString()); lvpageCount = int.Parse(sqlParam[3].Value.ToString()); return dt; }