ADO中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
相關文章
- ADO中sqlserver儲存過程使用 (轉)SQLServer儲存過程
- Sqlserver中的儲存過程SQLServer儲存過程
- 使用ADO呼叫儲存過程 (轉)儲存過程
- SQLSERVER儲存過程SQLServer儲存過程
- 使用ADO執行儲存過程 (轉)儲存過程
- SqlServer儲存過程的建立與使用SQLServer儲存過程
- SQLServer查詢使用者儲存過程SQLServer儲存過程
- SqlServer-儲存過程分頁SQLServer儲存過程
- sqlserver 常用儲存過程集錦SQLServer儲存過程
- C#二十六 使用Ado.Net呼叫儲存過程C#儲存過程
- 【SqlServer】清除過期資料的儲存過程SQLServer儲存過程
- 使用儲存過程儲存過程
- ADO & ADO.NET中使用儲存過程的兩個共用的函式 (轉)儲存過程函式
- sqlserver儲存過程實現多表分頁SQLServer儲存過程
- 解密SQLServer2005儲存過程解密SQLServer儲存過程
- MySQL儲存過程中如何使用ROLLBACKMySql儲存過程
- 在儲存過程A中呼叫儲存過程B的結果儲存過程
- SQLServer 2005通用分頁儲存過程SQLServer儲存過程
- SQL Server儲存過程中raiserror的使用SQLServer儲存過程AIError
- PB中呼叫儲存過程儲存過程
- 儲存過程中拼接字串儲存過程字串
- java中呼叫儲存過程Java儲存過程
- Mysql 儲存過程的使用MySql儲存過程
- 儲存過程中使用cursor儲存過程
- sqlserver資料庫還原儲存過程指令碼SQLServer資料庫儲存過程指令碼
- 【sqlserver】查詢阻塞儲存過程sp_who_lockSQLServer儲存過程
- sqlserver2008連結伺服器中執行儲存過程配置過程SQLServer伺服器儲存過程
- 儲存過程中巢狀儲存過程的變數執行方式儲存過程巢狀變數
- Hibernate中怎麼使用儲存過程呢?儲存過程
- 使用儲存過程(PL/SQL)向資料庫中儲存BLOB物件儲存過程SQL資料庫物件
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- 儲存過程儲存過程
- jsp中呼叫儲存過程JS儲存過程
- 儲存過程中慎用 execute immediate儲存過程
- java中呼叫ORACLE儲存過程JavaOracle儲存過程
- mssql sqlserver 批量刪除所有儲存過程的方法分享SQLServer儲存過程
- SQLSERVER儲存過程如何寫帶引數的遊標SQLServer儲存過程
- Laravel 中使用 MySQL 儲存過程LaravelMySql儲存過程