在经典的asp中执行存储过程并返回值
我需要从一个asp页面调用我在SQL 2008中存储的过程,并向它传递两个值,并在最后返回两个变量的计数。在经典的asp中执行存储过程并返回值
这是存储过程我有:
/****** STUDATATAB******/
CREATE PROCEDURE StdntReconcileDups @NewSTDNT int = NULL, @OldSTDNT int = NULL output
AS
--use ZLDP01RD;
--count = 9 rows
Select count (*) from STUDATA.dbo.STUDATATAB
where STDNT = @NewSTDNT;
-- count = 576 rows
select count(*) from STUDATA.dbo.STUDATATAB
where STDNT = @OldSTDNT;
-- select duplicate keys with new student# count = 3 rows
select a.STDNT,a.crs,a.CRS_VRSN,a.QSTN,a.SCR
from STUDATA.dbo.STUDATATABa, STUDATA.dbo.STUDATATABb
where a.STDNT = @NewSTDNT
and b.STDNT = @OldSTDNT
and a.crs = b.crs
and a.CRS_VRSN=b.CRS_VRSN
and a.QSTN=b.QSTN
and a.SCR=b.SCR
-- select duplicate keys with new student# count = 3 rows
select count (*)
from STUDATA.dbo.STUDATATABa
where exists (select 1 from STUDATA.dbo.STUDATATABb
where a.STDNT = @NewSTDNT
and b.STDNT = @OldSTDNT
and a.crs = b.crs
and a.CRS_VRSN=b.CRS_VRSN
and a.QSTN=b.QSTN
and a.SCR=b.SCR);
-- delete duplicate keys with new student# 3 rows deleted
WITH STUDENT_CTE
AS
(select a.*
from STUDATA.dbo.STUDATATABa
where exists (select 1 from STUDATA.dbo.STUDATATABb
where a.STDNT = @NewSTDNT
and b.STDNT = @OldSTDNT
and a.crs = b.crs
and a.CRS_VRSN=b.CRS_VRSN
and a.QSTN=b.QSTN
and a.SCR=b.SCR))
delete from STUDENT_CTE;
--Convert student #10826 history records to student #123196, should update 579 rows
UPDATE STUDATA.dbo.STUDATATAB
SET STDNT = @NewSTDNT, LSTUPDT_USER_ID_CD = 'DFERN', LSTUPDT_TS = getDate()
where STDNT = @OldSTDNT;
-- count= 582
select count(*) from STUDATA.dbo.STUDATATAB
where STDNT = @NewSTDNT;
-- count= 0
Select count(*) from STUDATA.dbo.STUDATATAB
where STDNT = @OldSTDNT;
go
我想插入代码中,如果参数,所以它调用StdntReconcileDups并传递给它KeepSTDNT的价值观和RemoveSTDNT
Dim DBConn ' Connection object
If Request.Form("Action") = "Go!" then
Endif
如果我得到你的意思......然后做象下面这样:
SqlConnection con = new SqlConnection("Data Source= ; initial catalog= Northwind ; User Id= ; Password= '");
con.open();
创建的SqlConnection后,您将创建StoredProcedure的象下面这样:
CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,@RegionDescription NCHAR(50)) AS
SET NOCOUNT OFF
UPDATE Region
SET RegionDescription = @RegionDescription
创建一个SqlCommand对象参数作为要执行的存储过程的名称以及命令将发送到的连接对象con来执行。
SqlCommand command = new SqlCommand("RegionUpdate",con);
更改命令对象命令类型存储过程属性。
command.CommandType = CommandType.StoredProcedure;
使用Parameters集合和SqlParameter类将参数添加到命令对象中。
command.Parameters.Add(new SqlParameter("@RegionID",SqlDbType.Int,0,"RegionID"));
command.Parameters.Add(new SqlParameter("@RegionDescription",SqlDbType.NChar,50,"RegionDescription"));
使用参数
command.Parameters[0].Value=4;
command.Parameters[1].Value="SouthEast";
Excecute使用它返回由存储过程实现的行数ExecuteNonQuery方法存储过程的Value属性指定的参数的值。
int i=command.ExecuteNonQuery();
上面的代码是正确的方式...你可以代替@KeepSTDNT和@RemoveSTDNT的正好代替@RegionID和@RegionDescription ...我已经用它自己。 ..如果你想更多的帮助...告诉然后
这个概念就是在执行你的请求......你将得到一个你将遍历的记录集,直到你到达eof ...然后你将调用下一个记录集来获得下一个结果。
...你在找这样的东西吗?
connectstring = "..."
sql = "StdntReconcileDups '" & NewSTDNT & "','" & OldSTDNT & "'"
set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open sql, connectstring, 3
这不是我正在寻找。 SQL编辑所有我需要的数据是一种使用KeepSTDNT和RemoveSTDNT值运行的方法 – user2381979 2013-05-14 17:37:48
是@OldStdnt意思是一个iput或输出参数 – 2013-05-15 06:09:55