ASP调用MSSQL存储过程并返回记录集源码详解

有人知道asp如何调用mssql存储过程吗，需要给个例子，我才学的会

答案：
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%> 
<!--#include file="adovbs.inc"--> 
<% 
'通用的ASP读取MSSQL存储过程代码示例 
'返回临时表记录集 
'带传递参数 
dim conn,connstr,cmd,rs 
connstr = "Provider=SQLOLEDB;server=服务器计算机名或IP地址;uid=用户名;pwd=密码;DATABASE=数据库名称;" '连接数据库字符串 
Set conn = Server.CreateObject("ADODB.Connection") '创建conn对象 
conn.open connstr '打开数据库 
Set Cmd=server.CreateObject("Adodb.Command") '创建command对象 
Cmd.ActiveConnection=conn 
Cmd.CommandText="存储过程名" '调用存储过程名称 
Cmd.CommandType=4 
cmd.prepared=true 
Cmd.Parameters.Append Cmd.CreateParameter("fyear",adInteger,1,2,"2007")  '示例传递给存储过程一个年份,类型是integer,对应存储过程中的@fyear变量 
Cmd.Parameters.Append Cmd.CreateParameter("myname",adVarChar,adParamInput,50,"阿里西西") '示例传递给存储过程一个中文字符,类型是varchar,长度为50,对应存储过程中的@myname变量 
set rs=Cmd.execute 
'<!--循环显示返回记录集全部记录--> 
while not rs.eof 
response.write "<div>"&rs(0)&"|"&rs(1)&"|"&rs(2)&"</div>" 
rs.movenext 
wend 
'<!--循环显示返回记录集全部记录--> 
'<!--释放对象--> 
rs.close 
set rs = nothing 
conn.close 
set conn = nothing 
set cmd = nothing 
'<!--释放对象--> 
%>

以下是存储过程代码：
SET QUOTED_IDENTIFIER OFF  
GO 
SET ANSI_NULLS OFF  
GO 
ALTER PROCEDURE 存储过程名  
( 
@fyear int, 
@myname varchar(50) 
) 
AS 
set nocount on 
begin 
--创建临时表 
create table #tmp_demo(tmp_fyear int,tmp_name varchar(50),tmp_info varchar(2000),tmp_sum int,tmp_int int) 
insert into #tmp_demo select fyear,fname,finfo,sum(fsum), 
isnull((select fint from tb2 where fid=tb1.fid and fname=tb1.fname),0)  
from tb1 
group by fname order by fname 
--示例判断并返回记录 
if (@fyear<>'' and @myname='') 
select * from #tmp_demo where tmp_fyear = @fyear order by tmp_sum desc 
else if (@fyear<>'' and @myname<>'') 
select * from #tmp_demo where tmp_fyear = @fyear and tmp_name = @myname order by tmp_sum desc 
else 
select * from #tmp_demo order by tmp_sum desc 
end 
GO 
SET QUOTED_IDENTIFIER OFF  
GO 
SET ANSI_NULLS ON  
GO 

其他：
adovbs.inc代码：
<% 
'-------------------------------------------------------------------- 
' Microsoft ADO 
' 
' (c) 1996 Microsoft Corporation.  All Rights Reserved. 
' 
' http://www.zzzyk.com/
' 
' ADO constants include file for VBScript 
' 
'-------------------------------------------------------------------- 
'---- CursorTypeEnum Values ---- 
Const adOpenForwardOnly = 0 
Const adOpenKeyset = 1 
Const adOpenDynamic = 2 
Const adOpenStatic = 3 
'---- CursorOptionEnum Values ---- 
Const adHoldRecords = &H00000100 
Const adMovePrevious = &H00000200 
Const adAddNew = &H01000400 
Const adDelete = &H01000800 
Const adUpdate = &H01008000 
Const adBookmark = &H00002000 
Const adApproxPosition = &H00004000 
Const adUpdateBatch = &H00010000 
Const adResync = &H00020000 
Const adNotify = &H00040000 
'---- LockTypeEnum Values ---- 
Const adLockReadOnly = 1 
Const adLockPessimistic = 2 
Const adLockOptimistic = 3 
Const adLockBatchOptimistic = 4 
'---- ExecuteOptionEnum Values ---- 
Const adRunAsync = &H00000010 
'---- ObjectStateEnum Values ---- 
Const adStateClosed = &H00000000 
Const adStateOpen = &H00000001 
Const adStateConnecting = &H00000002 
Const adStateExecuting = &H00000004 
'---- CursorLocationEnum Values ---- 
Const adUseServer = 2 
Const adUseClient = 3 
'---- DataTypeEnum Values ---- 
Const adEmpty = 0 
Const adTinyInt = 16 
Const adSmallInt = 2 
Const adInteger = 3 
Const adBigInt = 20 
Const adUnsignedTinyInt = 17 
Const adUnsignedSmallInt = 18 
Const adUnsignedInt = 19 
Const adUnsignedBigInt = 21 
Const adSingle = 4 
Const adDouble = 5 
Const adCurrency = 6 
Const adDecimal = 14 
Const adNumeric = 131 
Const adBoolean = 11 
Const adError = 10 
Const adUserDefined = 132 
Const adVariant = 12 
Const adIDispatch = 9 
Const adIUnknown = 13 
Const adGUID = 72 
Const adDate = 7 
Const adDBDate = 133 
Const adDBTime = 134 
Const adDBTimeStamp = 135 
Const adBSTR = 8 
Const adChar = 129 
Const adVarChar = 200 
Const adLongVarChar = 201 
Const adWChar = 130 
Const adVarWChar = 202 
Const adLongVarWChar = 203 
Const adBinary = 128 
Const adVarBinary = 204 
Const adLongVarBinary = 205 
'---- FieldAttributeEnum Values ---- 
Const adFldMayDefer = &H00000002 
Const adFldUpdatable = &H00000004 
Const adFldUnknownUpdatable = &H00000008 
Const adFldFixed = &H00000010 
Const adFldIsNullable = &H00000020 
Const adFldMayBeNull = &H00000040 
Const adFldLong = &H00000080 
Const adFldRowID = &H00000100 
Const adFldRowVersion = &H00000200 
Const adFldCacheDeferred = &H00001000 
'---- EditModeEnum Values ---- 
Const adEditNone = &H0000 
Const adEditInProgress = &H0001 
Const adEditAdd = &H0002 
Const adEditDelete = &H0004 
'---- RecordStatusEnum Values ---- 
Const adRecOK = &H0000000 
Const adRecNew = &H0000001 
Const adRecModified = &H0000002 
Const adRecDeleted = &H0000004 
Const adRecUnmodified = &H0000008 
Const adRecInvalid = &H0000010 
Const adRecMultipleChanges = &H0000040 
Const adRecPendingChanges = &H0000080 
Const adRecCanceled = &H0000100 
Const adRecCantRelease = &H0000400 
Const adRecConcurrencyViolation = &H0000800 
Const adRecIntegrityViolation = &H0001000 
Const adRecMaxChangesExceeded = &H0002000 
Const adRecObjectOpen = &H0004000 
Const adRecOutOfMemory = &H0008000 
Const adRecPermissionDenied = &H0010000 
Const adRecSchemaViolation = &H0020000 
Const adRecDBDeleted = &H0040000 
'---- GetRowsOptionEnum Values ---- 
Const adGetRowsRest = -1 
'---- PositionEnum Values ---- 
Const adPosUnknown = -1 
Const adPosBOF = -2 
Const adPosEOF = -3 
'---- enum Values ---- 
Const adBookmarkCurrent = 0 
Const adBookmarkFirst = 1 
Const adBookmarkLast = 2 
'---- MarshalOptionsEnum Values ---- 
Const adMarshalAll = 0 
Const adMarshalModifiedOnly = 1 
'---- AffectEnum Values ---- 
Const adAffectCurrent = 1 
Const adAffectGroup = 2 
Const adAffectAll = 3 
'---- FilterGroupEnum Values ---- 
Const adFilterNone = 0 
Const adFilterPendingRecords = 1 
Const adFilterAffectedRecords = 2 
Const adFilterFetchedRecords = 3 
Const adFilterPredicate = 4 
'---- SearchDirection Values ---- 
Const adSearchForward = 1 
Const adSearchBackward = -1 
'---- ConnectPromptEnum Values ---- 
Const adPromptAlways = 1 
Const adPromptComplete = 2 
Const adPromptCompleteRequired = 3 
Const adPromptNever = 4 
'---- ConnectModeEnum Values ---- 
Const adModeUnknown = 0 
Const adModeRead = 1 
Const adModeWrite = 2 
Const adModeReadWrite = 3 
Const adModeShareDenyRead = 4 
Const adModeShareDenyWrite = 8 
Const adModeShareExclusive = &Hc 
Const adModeShareDenyNone = &H10 
'---- IsolationLevelEnum Values ---- 
Const adXactUnspecified = &Hffffffff 
Const adXactChaos = &H00000010 
Const adXactReadUncommitted = &H00000100 
Const adXactBrowse = &H00000100 
Const adXactCursorStability = &H00001000 
Const adXactReadCommitted = &H00001000 
Const adXactRepeatableRead = &H00010000 
Const adXactSerializable = &H00100000 
Const adXactIsolated = &H00100000 
'---- XactAttributeEnum Values ---- 
Const adXactCommitRetaining = &H00020000 
Const adXactAbortRetaining = &H00040000 
'---- PropertyAttributesEnum Values ---- 
Const adPropNotSupported = &H0000 
Const adPropRequired = &H0001 
Const adPropOptional = &H0002 
Const adPropRead = &H0200 
Const adPropWrite = &H0400 
'---- ErrorValueEnum Values ---- 
Const adErrInvalidArgument = &Hbb9 
Const adErrNoCurrentRecord = &Hbcd 
Const adErrIllegalOperation = &Hc93 
Const adErrInTransaction = &Hcae 
Const adErrFeatureNotAvailable = &Hcb3 
Const adErrItemNotFound = &Hcc1 
Const adErrObjectInCollection = &Hd27 
Const adErrObjectNotSet = &Hd5c 
Const adErrDataConversion = &Hd5d 
Const adErrObjectClosed = &He78 
Const adErrObjectOpen = &He79 
Const adErrProviderNotFound = &He7a 
Const adErrBoundToCommand = &He7b 
Const adErrInvalidParamInfo = &He7c 
Const adErrInvalidConnection = &He7d 
Const adErrStillExecuting = &He7f 
Const adErrStillConnecting = &He81 
'---- ParameterAttributesEnum Values ---- 
Const adParamSigned = &H0010 
Const adParamNullable = &H0040 
Const adParamLong = &H0080 
'---- ParameterDirectionEnum Values ---- 
Const adParamUnknown = &H0000 
Const adParamInput = &H0001 
Const adParamOutput = &H0002 
Const adParamInputOutput = &H0003 
Const adParamReturnValue = &H0004 
'---- CommandTypeEnum Values ---- 
Const adCmdUnknown = &H0008 
Const adCmdText = &H0001 
Const adCmdTable = &H0002 
Const adCmdStoredProc = &H0004 
'---- SchemaEnum Values ---- 
Const adSchemaProviderSpecific = -1 
Const adSchemaAsserts = 0 
Const adSchemaCatalogs = 1 
Const adSchemaCharacterSets = 2 
Const adSchemaCollations = 3 
Const adSchemaColumns = 4 
Const adSchemaCheckConstraints = 5 
Const adSchemaConstraintColumnUsage = 6 
Const adSchemaConstraintTableUsage = 7 
Const adSchemaKeyColumnUsage = 8 
Const adSchemaReferentialContraints = 9 
Const adSchemaTableConstraints = 10 
Const adSchemaColumnsDomainUsage = 11 
Const adSchemaIndexes = 12 
Const adSchemaColumnPrivileges = 13 
Const adSchemaTablePrivileges = 14 
Const adSchemaUsagePrivileges = 15 
Const adSchemaProcedures = 16 
Const adSchemaSchemata = 17 
Const adSchemaSQLLanguages = 18 
Const adSchemaStatistics = 19 
Const adSchemaTables = 20 
Const adSchemaTranslations = 21 
Const adSchemaProviderTypes = 22 
Const adSchemaViews = 23 
Const adSchemaViewColumnUsage = 24 
Const adSchemaViewTableUsage = 25 
Const adSchemaProcedureParameters = 26 
Const adSchemaForeignKeys = 27 
Const adSchemaPrimaryKeys = 28 
Const adSchemaProcedureColumns = 29 
%> 

上一个：do while 循环
下一个：基于jQuery的简单菜单