ADO中sqlserver存储过程使用 (转)[@more@]

从ADO中得到多个记录集以及怎么样在ADO中使用sql SERVER 的存储过程

DataType Value Length Data Length BIGINT 996857543543543 15 8 INT 543543 6 4 SMALLINT 32765 5 2 TINYINT 254 3 1 BIT True 1 1 DECIMAL 765.5432321 11 9 NUMERIC 432.6544 8 5 MONEY 543.1234 6 8 SMALLMONEY 543.1234 6 4 FLOAT 5.4E+54 8 8 REAL 2.43E+24 9 4 DATETIME 8/31/2003 11:55:25 PM 19 8 SMALLDATETIME 8/31/2003 11:55:00 PM 19 4 CHAR QWE 3 4 VARCHAR Variable! 9 9 TEXT     307 NCHAR Wide 4 8 NVARCHAR   0 0 NTEXT     614 GUID {58F94A80-B839-4B35-B73C-7F4B4D336C3C} 36 16
Return Value: 0

CREATE PROCEDURE "dbo"."DataTypeTester" @myBigInt bigint , @myInt int , @mySmallint smallint , @myTinyint tinyint , @myBit bit , @myDecimal decimal(10, 7) , @myNumeric numeric(7, 4) , @myMoney money , @mySmallMoney smallmoney , @myFloat float , @myReal real , @myDatetime datetime , @mySmallDatetime smalldatetime , @myChar char(4) , @myVarchar varchar(10) , @myText text , @myNChar nchar(4) , @myNVarchar nvarchar(10) , @myNText ntext , @myGuid uniqueidentifier AS select 'BIGINT' "DataType", @myBigInt "Value" , LEN(@myBigInt) "Length" , DATALENGTH(@myBigInt) "Data Length" SELECT 'INT' , @myInt , LEN(@myInt) , DATALENGTH(@myInt) SELECT 'SMALLINT' , @mySmallint , LEN(@mySmallint) , DATALENGTH(@mySmallint) SELECT 'TINYINT' , @myTinyint , LEN(@myTinyint) , DATALENGTH(@myTinyint) SELECT 'BIT' , @myBit , LEN(@myBit) , DATALENGTH(@myBit) SELECT 'DECIMAL' , @myDecimal , LEN(@myDecimal) , DATALENGTH(@myDecimal) SELECT 'NUMERIC' , @myNumeric , LEN(@myNumeric) , DATALENGTH(@myNumeric) SELECT 'MONEY' , @myMoney , LEN(CAST(@mySmallMoney as varchar)) , DATALENGTH(@myMoney) SELECT 'SMALLMONEY' , @mySmallMoney , LEN(CAST(@mySmallMoney as varchar)) , DATALENGTH(@mySmallMoney) SELECT 'FLOAT' , @myFloat , LEN(@myFloat) , DATALENGTH(@myFloat) SELECT 'REAL' , @myReal , LEN(@myReal) , DATALENGTH(@myReal) SELECT 'DATETIME' , @myDatetime , LEN(@myDatetime) , DATALENGTH(@myDatetime) SELECT 'SMALLDATETIME' , @mySmallDatetime , LEN(@mySmallDatetime) , DATALENGTH(@mySmallDatetime) SELECT 'CHAR' , @myChar , LEN(@myChar) , DATALENGTH(@myChar) SELECT 'VARCHAR' , @myVarchar , LEN(@myVarchar) , DATALENGTH(@myVarchar) SELECT 'TEXT' , '' , '' , DATALENGTH(@myText) SELECT 'NCHAR' , @myNChar , LEN(@myNChar) , DATALENGTH(@myNChar) SELECT 'NVARCHAR' , @myNVarchar , LEN(@myNVarchar) , DATALENGTH(@myNVarchar) SELECT 'NTEXT' , '' , '' , DATALENGTH(@myNText) SELECT 'GUID' , @myGuid , LEN(@myGuid) , DATALENGTH(@myGuid) -- TODO: READTEXT should do this... /* , @myText "text" , @myNText "ntext" */ RETURN(0)

Code:<a href="leibie-6-1.html">ASP</a></a>" --&gt RM </a>'As ADODB.Parameter Dim rs 'As ADODB.Recordset Dim ret 'As Long Dim proc 'As String Dim allData() 'As Variant Dim colNames() 'As Variant Dim i 'As Long Dim datetime 'As DateTime Const StoredProcedure = "[dbo].[DataTypeTester]" Const titleString = "<html><head><title>ADO Parameter Test 3 / Multiple Recordset Tester</title><link rel=""stylesheet"" href=""/Templates/style.css"" type=""text/css"" /" /" /></head><body><h3>A example of how to retrieve multiple recordsets from ADO and how to set parameters in ADO for <a href="leibie-8-1.html">SQL SERVER</a> Stored Procedures</h3>" ReDim allData(0) ' initialize array dimension datetime = Now() Response.Write titleString Set conn = Server.Create<a href="tag-252-1.html">object</a>("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") conn.Open Application("connectionString") With cmd Set .ActiveConnection = conn .CommandText = StoredProcedure ' always use ADO constants .CommandType = adCmdStoredProc ' Check into the NamedParameters property at some point ' It doesn't require the order to be enforced, but it is always a good idea to enforce it anyway (for the documentation aspect of coding) ' RETURN parameter needs to be first .Parameters.Append cmd.CreateParameter("RETURN", adInteger, adParamReturnValue, 4) .Parameters.Append .CreateParameter("@myBigInt", adBigInt, adParamInput, 8, 996857543543543) .Parameters.Append .CreateParameter("@myInt", adInteger, adParamInput, 4, 543543) .Parameters.Append .CreateParameter("@mySmallint", adSmallInt, adParamInput, 2, 32765) .Parameters.Append .CreateParameter("@myTinyint", adTinyInt, adParamInput, 1, 254) .Parameters.Append .CreateParameter("@myBit", adBoolean, adParamInput, 4, True) ' Only Decimal and Numeric needs Precision and NumericScale .Parameters.Append .CreateParameter("@myDecimal", adDecimal, adParamInput, 9, 765.5432321) With .Parameters.Item("@myDecimal") .Precision = 10 .NumericScale = 7 End With Set prm = .CreateParameter("@myNumeric", adNumeric, adParamInput, 5, 432.6544) prm.Precision = 7 prm.NumericScale = 4 .Parameters.Append prm Set prm = Nothing .Parameters.Append .CreateParameter("@myMoney", adCurrency, adParamInput, 8, 543.1234) .Parameters.Append .CreateParameter("@mySmallMoney", adCurrency, adParamInput, 4, 543.1234) .Parameters.Append .CreateParameter("@myFloat", adDouble, adParamInput, 8, 5.4E+54) .Parameters.Append .CreateParameter("@myReal", adSingle, adParamInput, 4, 2.43E+24) .Parameters.Append .CreateParameter("@myDatetime", adDBTimeStamp, adParamInput, 8, datetime) .Parameters.Append .CreateParameter("@mySmallDatetime", adDBTimeStamp, adParamInput, 4, datetime) .Parameters.Append .CreateParameter("@myChar", adChar, adParamInput, 4, "QWE") .Parameters.Append .CreateParameter("@myVarchar", adVarchar, adParamInput, 10, "Variable!") .Parameters.Append .CreateParameter("@myText", adLongVarChar, adParamInput, Len(titleString)) .Parameters.Item("@myText").AppendChunk titleString .Parameters.Append .CreateParameter("@myNChar", adWChar, adParamInput, 4, "WIDE") .Parameters.Append .CreateParameter("@myNVarchar", adVarWchar, adParamInput, 10, "") .Parameters.Append .CreateParameter("@myNText", adLongVarWChar, adParamInput, Len(titleString)) .Parameters.Item("@myNText").AppendChunk titleString ' note the difference in these - without the {} the string implicitly converts ' the adVarChar version is of course commented out '.Parameters.Append .CreateParameter("@myGuid", adVarChar, adParamInput, 36, "58F94A80-B839-4B35-B73C-7F4B4D336C3C") .Parameters.Append .CreateParameter("@myGuid", adGUID, adParamInput, 16, "{58F94A80-B839-4B35-B73C-7F4B4D336C3C}") Set rs = .Execute 'get column names ReDim colNames(rs.Fields.Count - 1) For i = 0 to rs.Fields.Count - 1 colNames(i) = rs.Fields.Item(i).Name Next Do While Not (rs Is Nothing) ' get initial recordset If Not rs.EOF Then ' for retrieving more than about 30 or so recordsets you would probably want to use a collection allData(UBound(allData)) = rs.GetRows(adGetRowsRest) End If ' this will be nothing if no recordset is returned Set rs = rs.NextRecordset ' resize array if needed If Not (rs Is Nothing) Then ReDim Preserve allData(UBound(allData) + 1) L<a href="tag-257-1.html">oop</a> ' must release the recordset before retrieving output parameters and/or the return value ReleaseObj rs, True, True ret = CStr(.Parameters.Item("RETURN").Value) End With ReleaseObj cmd, False, True ReleaseObj conn, True, True ' show stored procedure proc = GetStoredProcedureDefinition(StoredProcedure) With Response outputNamedGetRowsArray allData, colNames .Write "<br />" .Write "Return Value: " & ret & "<br /><br />" .Write "<p>" & proc & "</pre>" End With displayAspFile Server.MapPath("adodb.command3.asp") Response.Write "</body></html>" %>