ADO中sqlserver儲存過程使用

sunnylovecmc發表於2010-01-20
SQLServer儲存過程

ADO中sqlserver儲存過程使用 收藏
從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:
<!--#include virtual="/testsite/global_include.asp" -->
<%
Dim conn 'As ADODB.Connection
Dim cmd 'As ADODB.Command
Dim prm '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><div align=""left""><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 dimension

datetime = Now()

Response.Write titleString

Set 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 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 "<pre>" & proc & "</pre>"
End With

displayAspFile Server.MapPath("adodb.command3.asp")

Response.Write "</div></body></html>"
%>

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/shadowkiss/archive/2003/09/10/13055.aspx

相關文章