一、存储过程返回值
例子一:
CREATE PROCEDURE [TotalUser]
@UserName varchar(40) output,
@ID int
as
set nocount on
begin
if @ID is null return
select @UserName=姓名 from 用户 where id=@ID
if @@rowcount>0
return 1
else
return 0
return
end
GO
<%
'**调用同时具有返回值、输入参数、输出参数的存储过程**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = Application("cwjh_usermdb") 'MyConStr是数据库连接字串
.CommandText = "TotalUser" '指定存储过程名
.CommandType = 4 '表明这是一个存储过程
.Prepared = true '要求将SQL命令先行编译
'返回值要最先被声明
.Parameters.Append .CreateParameter("RETURN",2,4)
'以下两参数的声明顺序也做相应颠倒
.Parameters.append .CreateParameter("@UserName",200,2,40)
.Parameters.append .CreateParameter("@UserID",3,1,4,UserID)
.Execute
end with
if MyComm(0) = 1 then
UserName = MyComm(1)
else
UserName = "该用户不存在"
end if
Set MyComm = Nothing
response.Write(username)
%>
例子二
CREATE PROCEDURE [test]
@t int output
AS
select @t=(select count(*) from 用户)
GO
<%
'**调用同时具有返回值、输入参数、输出参数的存储过程**
Set conn=Server.CreateObject("ADODB.CONNECTION")
Set rs=Server.CreateObject("ADODB.RecordSet")
conn.open Application("cwjh_usermdb")
rs.open "DECLARE @t int EXECUTE test @t output SELECT @t ",conn,1,1
response.Write(rs(0))
rs.close:set rs=nothing
conn.close:set conn=nothing
%>
二、TOP在存储过程中的写法
CREATE PROCEDURE [test1]
@N int
AS
begin
declare @str nvarchar(1000) --不能定义在varchar(1000),否则会出现:过程需要类型为 'ntext/nchar/nvarchar' 的参数 '提示出错
declare @strCounter varchar(10)
set @strCounter=convert(varchar(10),@N)
--select top 7 ThreadID,Topic from [BBSXP_Threads] where IsDel=0 and IsApproved=1 order by threadid desc
set @str='select top ' + @strCounter + ' ThreadID,Topic from [BBSXP_Threads] where IsDel=0 and IsApproved=1 order by threadid desc'
exec sp_executesql @str --要加sp_executesql,否则不能执行
end
GO
<%
'**调用同时具有返回值、输入参数、输出参数的存储过程**
Set conn=Server.CreateObject("ADODB.CONNECTION")
Set rs=Server.CreateObject("ADODB.RecordSet")
conn.open Application("cwjh_usermdb")
rs.open "EXECUTE test1 10 ",conn,1,1
do while not rs.eof
response.Write(rs(0)&"<br>")
rs.movenext
loop
rs.close:set rs=nothing
conn.close:set conn=nothing
%>