--呵呵,有的是时间 --建表 create table tb ( tId int, Salary decimal(17,4) )go insert tb select 1,800 union all select 2,1500 union all select 3,3200 union all select 4,5000go --创建存储过程 create proc tbTotalSalary ( @ReturnCode int=0 output, --返回值 @ReturnTotalSalary decimal(17,3)=0 output --返回值 ) as select @ReturnTotalSalary=sum(Salary) from tbselect @ReturnCode=@@error return @ReturnCode'VB.net代码: Try Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection("server=yangys;uid=sa;pwd=;database=mydbexersise") cn.Open() Dim cm As SqlClient.SqlCommand = New SqlClient.SqlCommand("tbTotalSalary", cn) cm.CommandType = CommandType.StoredProcedure '添加存储过程参数 Dim param As New SqlClient.SqlParameter param = cm.Parameters.Add("@ReturnCode", SqlDbType.Int) param.Value = 0 param.Direction = ParameterDirection.Output param = New SqlClient.SqlParameter param = cm.Parameters.Add("@ReturnTotalSalary", SqlDbType.Decimal) param.Value = 0 param.Direction = ParameterDirection.Output cm.ExecuteNonQuery() '获取返回值 TextBox1.Text = cm.Parameters("@ReturnTotalSalary").Value Catch ex As Exception MessageBox.Show(ex.Message) End Try
这是去年我们的BLOG中的一段关于分页的存储过程CREATE PROCEDURE SP_Page @TB VARCHAR(50), @COL VARCHAR(50), @ORDERBY BIT, @COLLIST VARCHAR(800), @PAGESIZE INT, @PAGE INT, @CONDITION VARCHAR(800), @RecPages INT, @RecCount INT OUTPUT, @PAGES INT OUTPUT, @OUTSQL NVARCHAR(4000) OUTPUTASDECLARE @SQL NVARCHAR(4000) DECLARE @WHERE1 VARCHAR(800) DECLARE @WHERE2 VARCHAR(800)IF @CONDITION IS NULL OR RTRIM(@CONDITION) = '' BEGIN SET @WHERE1=' WHERE ' SET @WHERE2=' ' END ELSE BEGIN SET @WHERE1=' WHERE ('+@CONDITION+') AND ' SET @WHERE2=' WHERE ('+@CONDITION+') ' ENDSET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2IF @RecPages = 0 EXEC SP_EXECUTESQL @SQL,N'@PAGES INT OUTPUT,@RecCount INT OUTPUT',@PAGES OUTPUT,@RecCount OUTPUT ELSE SELECT @PAGES = @RecPagesIF @ORDERBY=0 SET @SQL= 'SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'>(SELECT MAX('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+ @COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+') t) ORDER BY '+@COL ELSE SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'<(SELECT MIN('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+ @COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+' DESC) t) ORDER BY '+@COL+' DESC'IF @PAGE=1 SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC' ENDSET @OUTSQL = @SQLEXEC(@SQL) 下面是一段获取用户列表的方法的代码,里面调用了这个存储过程 public IList GetUserList(int pageSize, int pageNo) { IList list = new ArrayList(); string tb = "UserData"; string col = "ID"; string orderby = "true"; string collist = "ID,UserName,UserPass,Email,Title,Intro,TempletID,ViewStat,PageSize,ViewCount,IsPass,LoginCount"; string condition = "";
--建表
create table tb
(
tId int,
Salary decimal(17,4)
)go insert tb
select 1,800
union all
select 2,1500
union all
select 3,3200
union all
select 4,5000go
--创建存储过程
create proc tbTotalSalary
(
@ReturnCode int=0 output, --返回值
@ReturnTotalSalary decimal(17,3)=0 output --返回值
)
as
select
@ReturnTotalSalary=sum(Salary)
from tbselect @ReturnCode=@@error
return @ReturnCode'VB.net代码:
Try
Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection("server=yangys;uid=sa;pwd=;database=mydbexersise")
cn.Open()
Dim cm As SqlClient.SqlCommand = New SqlClient.SqlCommand("tbTotalSalary", cn) cm.CommandType = CommandType.StoredProcedure
'添加存储过程参数
Dim param As New SqlClient.SqlParameter
param = cm.Parameters.Add("@ReturnCode", SqlDbType.Int)
param.Value = 0
param.Direction = ParameterDirection.Output
param = New SqlClient.SqlParameter
param = cm.Parameters.Add("@ReturnTotalSalary", SqlDbType.Decimal)
param.Value = 0
param.Direction = ParameterDirection.Output cm.ExecuteNonQuery() '获取返回值
TextBox1.Text = cm.Parameters("@ReturnTotalSalary").Value Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
@COL VARCHAR(50),
@ORDERBY BIT,
@COLLIST VARCHAR(800),
@PAGESIZE INT,
@PAGE INT,
@CONDITION VARCHAR(800),
@RecPages INT,
@RecCount INT OUTPUT,
@PAGES INT OUTPUT,
@OUTSQL NVARCHAR(4000) OUTPUTASDECLARE @SQL NVARCHAR(4000)
DECLARE @WHERE1 VARCHAR(800)
DECLARE @WHERE2 VARCHAR(800)IF @CONDITION IS NULL OR RTRIM(@CONDITION) = ''
BEGIN
SET @WHERE1=' WHERE '
SET @WHERE2=' '
END
ELSE
BEGIN
SET @WHERE1=' WHERE ('+@CONDITION+') AND '
SET @WHERE2=' WHERE ('+@CONDITION+') '
ENDSET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2IF @RecPages = 0
EXEC SP_EXECUTESQL @SQL,N'@PAGES INT OUTPUT,@RecCount INT OUTPUT',@PAGES OUTPUT,@RecCount OUTPUT
ELSE
SELECT @PAGES = @RecPagesIF @ORDERBY=0
SET @SQL= 'SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'>(SELECT MAX('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+
@COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+') t) ORDER BY '+@COL
ELSE
SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'<(SELECT MIN('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+
@COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+' DESC) t) ORDER BY '+@COL+' DESC'IF @PAGE=1
SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC' ENDSET @OUTSQL = @SQLEXEC(@SQL)
下面是一段获取用户列表的方法的代码,里面调用了这个存储过程 public IList GetUserList(int pageSize, int pageNo)
{
IList list = new ArrayList();
string tb = "UserData";
string col = "ID";
string orderby = "true";
string collist = "ID,UserName,UserPass,Email,Title,Intro,TempletID,ViewStat,PageSize,ViewCount,IsPass,LoginCount";
string condition = "";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Sp_Page";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = ICon.Cnn;
SqlParameter parm = cmd.Parameters.Add("@TB", System.Data.SqlDbType.NVarChar, 50);
parm.Value = tb;
parm = cmd.Parameters.Add("@COL", System.Data.SqlDbType.NVarChar, 50);
parm.Value = col;
parm = cmd.Parameters.Add("@ORDERBY", System.Data.SqlDbType.Bit, 5);
parm.Value = orderby;
parm = cmd.Parameters.Add("@COLLIST", System.Data.SqlDbType.NVarChar, 800);
parm.Value = collist;
parm = cmd.Parameters.Add("@PAGESIZE", System.Data.SqlDbType.Int, 4);
parm.Value = pageSize;
parm = cmd.Parameters.Add("@PAGE", System.Data.SqlDbType.Int, 8);
parm.Value = pageNo;
parm = cmd.Parameters.Add("@CONDITION", System.Data.SqlDbType.NVarChar, 800);
parm.Value = condition;
parm = cmd.Parameters.Add("@RecPages", System.Data.SqlDbType.Int,1);
parm.Value = 0;
cmd.Parameters.Add("@RecCount", System.Data.SqlDbType.Int, 8);
cmd.Parameters["@RecCount"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@PAGES", System.Data.SqlDbType.Int, 8);
cmd.Parameters["@PAGES"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@OUTSQL", System.Data.SqlDbType.NVarChar, 4000);
cmd.Parameters["@OUTSQL"].Direction = ParameterDirection.Output; SqlDataReader dr = cmd.ExecuteReader(); while(dr.Read())
{
UserInfo userinfo = new UserInfo(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4),
dr.GetString(5), dr.GetString(6), dr.GetByte(7), dr.GetInt32(8),dr.GetInt32(9), dr.GetByte(10), dr.GetInt32(11));
list.Add(userinfo);
}
dr.Close();
ICon.Close();
_recordcount = Int32.Parse(cmd.Parameters["@RecCount"].Value.ToString());
return list;
}
ADOQuery.ParamByName(ReturnValue).asString; //绝对值