在 vb 中调用 sql2000 存储过程A ,该存储过程要达到3个目的:1 按指定的条件查询并返回所有符合条件的记录;
2 返回所有符合条件记录字段 '重量' 的总数;
3 返回所有符合条件记录字段 '件数' 的总数;现在第1条已实现,问题是如何返回 总重量 和 总件数 ? 谢谢
2 返回所有符合条件记录字段 '重量' 的总数;
3 返回所有符合条件记录字段 '件数' 的总数;现在第1条已实现,问题是如何返回 总重量 和 总件数 ? 谢谢
(
@p1 int,
@p2 int,
@p3 int output ,
@p4 int output,
@p5 int output
)
asset @p3 = (select count(*) from 表名....)set @p4 = (select sum(重量) from 表名 where ...)set @p5 = (select sum(件数) from 表名 where ...)go
declare @int int
set @int = 1232432332
return @intgo
declare @Getint int
exec @Getint=Returnvalue
print @Getint
(
--其它参数,
@weight int output,
@quantity int output
)
as--1
select * from 表 where ...--2,3
select @weight=sum(重量),@quantity=sum(件数) from 表 where ...go
@LastName NVARCHAR(50),
@LastNameCount INT OUTPUT )
AS
SELECT @LastNameCount = COUNT(*)
FROM Person.Contact
WHERE LastName = @LastNameThen you can use ASP.NET to return both the select result and the output parameters:// Your code should get the connection string from web.config
string connectionString = @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("dbo.GetCountByLastName"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander")); SqlParameter countParameter = new SqlParameter("@LastNameCount", 0);
countParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(countParameter); conn.Open();
cmd.Connection = conn; cmd.ExecuteNonQuery();
int count = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
Response.Write("<p>Count: " + count.ToString());
conn.Close();
}
}
For select result, you need to use cmd.ExecuteReader, but for output parameters, you should use cmd.ExecuteNonQuery(). Is there any way to return both of them?CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT ContactID,
FirstName,
LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID
// Your code should get the connection string from web.config
string connectionString = @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("dbo.GetPeopleByLastName"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));
conn.Open();
cmd.Connection = conn;
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
int contactID = rdr.GetInt32(rdr.GetOrdinal("ContactID"));
string firstName = rdr.GetString(rdr.GetOrdinal("FirstName"));
Response.Write("<p>" + firstName + " (" + contactID.ToString() + ")");
}
rdr.Close();
}
}
}
(product varchar(10), weight float, pieces int)insert ta
select 'TV', 3, 5
union all select 'Fridge', 30, 2
union all select 'Desk', 5, 8
union all select 'bed', 6, 3
union all select 'TV', 4.5, 8create Procedure GetInfoByProduct
(
@product nvarchar(50)
)
AS
BEGIN
SELECT product, weight, pieces
From ta
Where product = @product
COMPUTE sum(weight), sum(pieces)
END
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;namespace SP_Test1
{
class Program
{
static void Main(string[] args)
{
// Your code should get the connection string from web.config
string connectionString = @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("dbo.GetInfoByProduct"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@product", "TV"));
conn.Open();
cmd.Connection = conn; using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
string product = rdr.GetString(rdr.GetOrdinal("product"));
double weight = rdr.GetDouble(rdr.GetOrdinal("weight"));
int pieces = rdr.GetInt32(rdr.GetOrdinal("pieces"));
Console.WriteLine("product = {0}, weight = {1}, pieces = {2}", product, weight, pieces);
} rdr.NextResult();
while (rdr.Read())
{
double t1 = rdr.GetDouble(0);
int t2 = rdr.GetInt32(1);
Console.WriteLine("total weight = {0}, total pieces = {1}", t1, t2);
} rdr.Close();
}
}
} }
}
}
(product varchar(10), weight int, pieces int)insert ta
select 'TV', 3, 5
union all select 'Fridge', 30, 2
union all select 'Desk', 5, 8
union all select 'bed', 6, 3
union all select 'TV', 4, 8Create Procedure GetInfoByProduct
(
@product nvarchar(50)
)
AS
BEGIN
SELECT product, weight, pieces
From ta
Where product = @product
COMPUTE sum(weight), sum(pieces)
END
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClientModule Module1 Sub Main()
'Your code should get the connection string from web.config
Dim connectionString As String = "Server=PANDA\SQLEXPRESS; Initial Catalog=AdventureWorks; Integrated Security=True;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand("dbo.GetInfoByProduct")
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@product", "TV"))
conn.Open()
cmd.Connection = conn Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While rdr.Read()
Dim product As String = rdr.GetString(rdr.GetOrdinal("product"))
Dim weight As Int32 = rdr.GetInt32(rdr.GetOrdinal("weight"))
Dim pieces As Int32 = rdr.GetInt32(rdr.GetOrdinal("pieces"))
Console.WriteLine("product = {0}, weight = {1}, pieces = {2}", product, weight, pieces)
End While
rdr.NextResult()
While rdr.Read()
Dim t1 As Int32 = rdr.GetInt32(0)
Dim t2 As Int32 = rdr.GetInt32(1)
Console.WriteLine("total weight = {0}, total pieces = {1}", t1, t2)
End While
'rdr.Close()
End SubEnd Module