如何在ASP里调用sql server的存储过程

[coollzh]

在ASP里如何调用SQL SERVER里写的存储过程，如何传递变量，如何返回查询结果
给个例子研究研究

[wwl007]

为什么这吗长阿

[coollzh]

hsboy(hsboy)　:
网上哪里有比较完整的例子

[hsboy]

<%
Dim objConn,objCmd,objRst
Set objConn=Server.CreateObject("ADODB.Connection")
Set objCmd=Server.CreateObject("ADODB.Command")

objConn.ConnectionString="Server=xxx;Driver={SQL SERVER};Database=xxx;UID=xxx;PWD=xxx"
objConn.Open

objCmd.ActiveConnection=objConn

objCmd.CommandType=adCmdStoredProc
objCmd.CommandText="Your_Stored_Procedure_Name"
'If your procedure need parameters, then add them
objCmd.Parameters.Append objCmd.CreateParameter("@Parameter_Name",Your_Parameter_Data_Type,Your_Parameter_Type,Length,Value)
......

Set objRst=objCmd.Execute
'Now objRst Stores the record set that the procedure returned
'End of Example
---------------------------------------
Your_Parameter_Data_Type can be adInteger,adTinyint, adVarChar, etc.
Your_Parameter_Type can be adParamInput, adParamOutPut, adParamInputOutPut, adParamReturnValue, adParamUnKnown.
Length is the size of the parameter, for example, if the data type is adInteger, Length should be 4, else if adVarChar, Length should be the actual siz of this parameter.
The following is some examples of append parameters to the command object.

'return value
objCmd.Parameters.Append objCmd.CreateParameter("@rc",adInteger,adParamReturnValue)
'name varchar(50)
objCmd.Parameters.Append objCmd.CreateParameter("@name",adVarChar,adParamInput,50,strName)
'content text(16)
objCmd.Parameters.Append objCmd.CreateParameter("@content",adLongVarChar,adParamInput,9999999,strContent)
'age tinyint
objCmd.Parameters.Append objCmd.Createparameter("@age",adTinyint,adParamInput,2,22)

[hsboy]

这个比较难找。
我给你一个实例吧。
首先创建一个表供测试：
--begin of sql script
create table a
(
id int not null primary key identity(1,1),
account varchar(20) not null,
pwd varchar(20) not null
)
go
--end of sql script
然后创建一个存储过程
create proc addAccount(@account varchar(20),@pwd varchar(20))
as
insert into a (account,pwd) values(@account,@pwd)
go

然后，在asp中：
<%
Dim objConn,objCmd,objRst
Set objConn=Server.CreateObject("ADODB.Connection")
Set objCmd=Server.CreateObject("ADODB.Command")

objConn.ConnectionString="Server=xxx;Driver={SQL SERVER};Database=xxx;UID=xxx;PWD=xxx"
objConn.Open

objCmd.ActiveConnection=objConn

objCmd.CommandType=adCmdStoredProc
objCmd.CommandText="a"
objCmd.Parameters.Append objCmd.CreateParameter("@account","user1")
objCmd.Parameters.Append objCmd.CreateParameter("@pwd","123456")
objCmd.Execute
%>
另外，你需要注意赋予连接到数据库的用户（objConn.ConnectionString中的UID)执行该存储过程的权限（如果用sa则不必，因为它拥有一切权限）