ADO中sqlserver存储过程使用
来源：岁月联盟
时间：2003-09-10
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:
<%Dim conn 'As ADODB.ConnectionDim cmd 'As ADODB.CommandDim prm 'As ADODB.ParameterDim rs 'As ADODB.RecordsetDim ret 'As LongDim proc 'As StringDim allData() 'As VariantDim colNames() 'As VariantDim i 'As LongDim datetime 'As DateTimeConst 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 SQL Server Stored Procedures</h3>"ReDim allData(0) ' initialize array dimensiondatetime = Now()Response.Write titleStringSet conn = Server.CreateObject("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) Loop ' must release the recordset before retrieving output parameters and/or the return value ReleaseObj rs, True, True ret = CStr(.Parameters.Item("RETURN").Value)End WithReleaseObj cmd, False, TrueReleaseObj conn, True, True' show stored procedureproc = GetStoredProcedureDefinition(StoredProcedure)With Response outputNamedGetRowsArray allData, colNames .Write "<br />" .Write "Return Value: " & ret & "<br /><br />" .Write "<pre>" & proc & "</pre>"End WithdisplayAspFile Server.MapPath("adodb.command3.asp")Response.Write "</body></html>"%> 
