Stored Procedure | recordset | detailed
The following is the ASP code (demo.asp): <% @LANGUAGE = "VBSCRIPT" codepage= "936"%> <!--#include file= "Adovbs.inc"--> <% ' Common ASP reads the MSSQL stored procedure Code sample ' Returns a temporary table recordset ' With passing parameters Dim conn,connstr,cmd,rs CONNSTR = "provider=sqloledb;server= Server computer name or IP address; uid= user name; pwd= password; database= database name;" ' Connect database strings Set conn = Server.CreateObject ("ADODB. Connection ")" Create conn Object Conn.Open connstr ' Open database Set Cmd=server. CreateObject ("Adodb.command") ' Create Command Object Cmd.activeconnection=conn cmd.commandtext= ' stored procedure name ' calls the stored procedure name Cmd.commandtype=4 Cmd.prepared=true Cmd.Parameters.Append cmd.createparameter ("Fyear", adinteger,1,2, "2007") ' example is passed to a stored procedure for a year, the type is integer, @fyear variables in the corresponding stored procedure Cmd.Parameters.Append cmd.createparameter ("MyName", advarchar,adparaminput,50, "Arisisi") ' sample passed to a stored procedure in a Chinese character, type is varchar , with a length of 50, corresponding to the @myname variable in the stored procedure Set Rs=cmd.execute ' <!--loop display returns recordset all records--> While not rs.eof Response.Write "<div>" &rs (0) & "|" &rs (1) & "|" &rs (2) & "</div>" Rs.movenext Wend ' <!--loop display returns recordset all records--> ' <!--Release object--> Rs.close Set rs = Nothing Conn.close Set conn = Nothing Set cmd = Nothing ' <!--Release object--> %> |
here is the stored procedure code: SET QUOTED_IDENTIFIER OFF Go SET ansi_nulls off Go ALTER PROCEDURE Stored Procedure name ( @fyear int, @myname varchar (50) ) As SET NOCOUNT ON Begin --Create a temporary table CREATE TABLE #tmp_demo (tmp_fyear int,tmp_name varchar (), Tmp_info varchar (watts), 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 --Example to determine and return records 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 tmp_sum desc End Go SET QUOTED_IDENTIFIER OFF Go SET ANSI_NULLS on Go |
The following adovbs.inc code : <% ’-------------------------------------------------------------------- ' Microsoft ADO ’ ' (c) 1996 Microsoft Corporation. All Rights Reserved. ’ ’ ’ ' 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 %> |