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:
<%
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 = "ADO Parameter Test 3 / Multiple Recordset Tester A example of how to retrieve multiple recordsets from ADO and how to set parameters in ADO for SQL Server Stored Procedures
"
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 "
"
.Write "Return Value: " & ret & "
"
.Write "" & proc & ""
End With
displayAspFile Server.MapPath("adodb.command3.asp")
Response.Write "
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/