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,--查询条件 @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),121)+'''' END --确定当前总记录条数 set @vn_strSQL='SELECT @oi_RecordCount =COUNT(*) FROM [demo] where '+@in_Sqlwhere exec sp_executesql @vn_strSQL,@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",new SqlParameter("@oi_RecordCount",new SqlParameter("@oi_PageCount",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; }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!