存储过程查询多个不相关联的表,用多条select语句,调用时只能调用到第一个查询,第二个查询以后的字段就调用不到,怎么办,请指导
CREATE procedure sp_resume_edit@ss varchar (40)
AS
select a,b from xx where gh1= @ss order by idselect c,d from yy where gh2= @ss order by idselect e,f from zz where gh3= @ss order by idselect h,k from uu where gh4= @ss order by id
GO
调用如下:dim c,d,e,f
set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=aa;Database=cc1"
CmdSP.CommandText = "sp_resume_edit"
CmdSP.CommandType = adCmdSPStoredProc CmdSP.Parameters.Append CmdSP.CreateParameter("@se", advarchar,adParaminput,21,se)Set adoRS = CmdSP.Execute
a = adoRS("a")
b = adoRS("b")c = adoRS("c")
d = adoRS("d")
e = adoRS("e")f = adoRS("f")
CREATE procedure sp_resume_edit@ss varchar (40)
AS
select a,b from xx where gh1= @ss order by idselect c,d from yy where gh2= @ss order by idselect e,f from zz where gh3= @ss order by idselect h,k from uu where gh4= @ss order by id
GO
调用如下:dim c,d,e,f
set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=aa;Database=cc1"
CmdSP.CommandText = "sp_resume_edit"
CmdSP.CommandType = adCmdSPStoredProc CmdSP.Parameters.Append CmdSP.CreateParameter("@se", advarchar,adParaminput,21,se)Set adoRS = CmdSP.Execute
a = adoRS("a")
b = adoRS("b")c = adoRS("c")
d = adoRS("d")
e = adoRS("e")f = adoRS("f")
然后
dataset.datatable[0]
dataset.datatable[1]
打开企业管理器，在数据库 bookmanage 中新建存储过程：
CREATE PROCEDURE authorlist AS
DECLARE @allauthor varchar(8000),
@authorid integer,
@name varchar(10)
DECLARE curAuthor CURSOR
FOR SELECT 作者编号,姓名 FROM 作者表
OPEN curAuthor
FETCH curAuthor INTO @authorid, @name
SELECT @allauthor = ''
WHILE @@fetch_status = 0
BEGIN
SELECT @allauthor = @allauthor + '<option value = "'
+ convert(varchar(10),@authorid) + '">' + @name + '</option>'
FETCH curAuthor INTO @authorid, @name
END
CLOSE curAuthor
DEALLOCATE curAuthor
SELECT @allauthor AS TheList
GO
该存储过程使用游标对作者表中的作者编号和姓名进行查询，将获得的记录集用循环语句结合网页的HTML标记语言设置成下拉列表中的列表内容，最后以字符串形式返回给调用的ASP程序。通过服务器调用该存储过程的 ASP 程序如下：<%
dim spConn, spRS
set spConn = server.createobject("adodb.connection")
set spRS = server.createobject("ADODB.recordset")
spConn.connectionstring = "driver={SQL Server};" & _
"server=YCTC-SERVER;uid=sa;pwd=sqlserver;database=bookmanage"
spConn.open
spRS.open "execute authorlist", spConn
%>
<html>
<title>使用RecordSet对象调用存储过程</title>
<body>
<% '用下拉列表显示数据 %>
请选择作者姓名：
<select id="combox1" name="authorid">
<% response.write spRS("TheList") %>
</select>
</body>
</html>
<%
spRS.close
spConn.close
set spConn = nothing
%>