MSSQL2000中存储过程返回值及TOP在存储过程中的用法

 

---

一、存储过程返回值

---

 

例子一：

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
%>