ADO中sqlserver儲存過程使用 (轉)

ADO中sqlserver儲存過程使用 (轉)[@more@]

從ADO中得到多個記錄集以及怎麼樣在ADO中使用 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/ 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 W 4 8 NVARCHAR   0 0 NTEXT     614 GUID {58F94A80-B839-4B35-B73C-7F4B4D336C3C} 36 16
Return Value: 0

CREATE PROCEDURE "o"."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 '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:&quot; --&amp;gt RM '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 = &quot;[dbo].[DataTypeTester]&quot; Const titleString = &quot;<title>ADO Parameter Test 3 / Multiple Recordset Tester</title><link rel="" stylesheet="" href="http://blog.itpub.net/10752019/viewspace-982618/" type="" text=""><h3>A example of how to retrieve multiple recordsets from ADO and how to set parameters in ADO for Stored Procedures</h3>&quot; ReDim allData(0) ' initialize array dimension datetime = Now() Response.Write titleString Set conn = Server.Create(&quot;ADODB.Connection&quot;) Set cmd = Server.CreateObject(&quot;ADODB.Command&quot;) conn.Open Application(&quot;connectionString&quot;) 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(&quot;RETURN&quot;, adInteger, adParamReturnValue, 4) .Parameters.Append .CreateParameter(&quot;@myBigInt&quot;, adBigInt, adParamInput, 8, 996857543543543) .Parameters.Append .CreateParameter(&quot;@myInt&quot;, adInteger, adParamInput, 4, 543543) .Parameters.Append .CreateParameter(&quot;@mySmallint&quot;, adSmallInt, adParamInput, 2, 32765) .Parameters.Append .CreateParameter(&quot;@myTinyint&quot;, adTinyInt, adParamInput, 1, 254) .Parameters.Append .CreateParameter(&quot;@myBit&quot;, adBoolean, adParamInput, 4, True) ' Only Decimal and Numeric needs Precision and NumericScale .Parameters.Append .CreateParameter(&quot;@myDecimal&quot;, adDecimal, adParamInput, 9, 765.5432321) With .Parameters.Item(&quot;@myDecimal&quot;) .Precision = 10 .NumericScale = 7 End With Set prm = .CreateParameter(&quot;@myNumeric&quot;, adNumeric, adParamInput, 5, 432.6544) prm.Precision = 7 prm.NumericScale = 4 .Parameters.Append prm Set prm = Nothing .Parameters.Append .CreateParameter(&quot;@myMoney&quot;, adCurrency, adParamInput, 8, 543.1234) .Parameters.Append .CreateParameter(&quot;@mySmallMoney&quot;, adCurrency, adParamInput, 4, 543.1234) .Parameters.Append .CreateParameter(&quot;@myFloat&quot;, adDouble, adParamInput, 8, 5.4E+54) .Parameters.Append .CreateParameter(&quot;@myReal&quot;, adSingle, adParamInput, 4, 2.43E+24) .Parameters.Append .CreateParameter(&quot;@myDatetime&quot;, adDBTimeStamp, adParamInput, 8, datetime) .Parameters.Append .CreateParameter(&quot;@mySmallDatetime&quot;, adDBTimeStamp, adParamInput, 4, datetime) .Parameters.Append .CreateParameter(&quot;@myChar&quot;, adChar, adParamInput, 4, &quot;QWE&quot;) .Parameters.Append .CreateParameter(&quot;@myVarchar&quot;, adVarchar, adParamInput, 10, &quot;Variable!&quot;) .Parameters.Append .CreateParameter(&quot;@myText&quot;, adLongVarChar, adParamInput, Len(titleString)) .Parameters.Item(&quot;@myText&quot;).AppendChunk titleString .Parameters.Append .CreateParameter(&quot;@myNChar&quot;, adWChar, adParamInput, 4, &quot;WIDE&quot;) .Parameters.Append .CreateParameter(&quot;@myNVarchar&quot;, adVarWchar, adParamInput, 10, &quot;&quot;) .Parameters.Append .CreateParameter(&quot;@myNText&quot;, adLongVarWChar, adParamInput, Len(titleString)) .Parameters.Item(&quot;@myNText&quot;).AppendChunk titleString ' note the difference in these - without the {} the string implicitly converts ' the adVarChar version is of course commented out '.Parameters.Append .CreateParameter(&quot;@myGuid&quot;, adVarChar, adParamInput, 36, &quot;58F94A80-B839-4B35-B73C-7F4B4D336C3C&quot;) .Parameters.Append .CreateParameter(&quot;@myGuid&quot;, adGUID, adParamInput, 16, &quot;{58F94A80-B839-4B35-B73C-7F4B4D336C3C}&quot;) 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 ' must release the recordset before retrieving output parameters and/or the return value ReleaseObj rs, True, True ret = CStr(.Parameters.Item(&quot;RETURN&quot;).Value) End With ReleaseObj cmd, False, True ReleaseObj conn, True, True ' show stored procedure proc = GetStoredProcedureDefinition(StoredProcedure) With Response outputNamedGetRowsArray allData, colNames .Write &quot;<br>&quot; .Write &quot;Return Value: &quot; &amp; ret &amp; &quot;<br><br>&quot; .Write &quot;<p>&quot; &amp; proc &amp; &quot;&quot; End With displayAspFile Server.MapPath(&quot;adodb.command3.asp&quot;) Response.Write &quot;&quot; %&gt;</p>