1. Call stored procedures without parameters
<%
set conn=("")
set cmd=("")
strconn="dsn=pubs;uid=sa;pwd"
strconn
set =conn
="{call nono}"
'set rs= or
set rs=()
%>
2. A stored procedure for input parameters
<%
set conn=("")
set cmd=("")
strconn="dsn=pubs;uid=sa;pwd"
strconn
set =conn
="{call oneinput(?)}"
("@aaa",adInteger ,adParamInput )
cmd("@aaa")=100
()
%>
3. An input parameter and an output parameter
<%
set conn=("")
set cmd=("")
strconn="dsn=pubs;uid=sa;pwd"
strconn
set =conn
= "{call oneinout(?,?)}"
("@aaa",adInteger,adParamInput)
cmd("@aaa")=10
("@bbb",adInteger,adParamOutput)
()
bbb=cmd("@bbb")
%>
4. One input parameter, one output parameter, and one return value
<%
set conn=("")
set cmd=("")
strconn="dsn=pubs;uid=sa;pwd"
strconn
set =conn
="{?=call onereturn(?,?)}"
("@return_value",adInteger,adParamReturnValue )
("@aaa",adInteger,adParamInput )
cmd("@aaa")=10
("@bbb",adInteger,adParamOutput)
()
bbb=cmd("@bbb")
rrr=cmd("@return_value")
%>
How to call SQL stored procedure in ASP
2002-2-7 Jiaxiang365
<%set connection1 = ("")
... 'Join
set command1=("")
set =connection1
=4
="sp_1" 'SP name
(1)=... 'Parameter value
(2)=...
set recordset1=()
%>
Tips for ASP calling stored procedures
1. The simplest one is as follows
Dim objConn
Set objConn = ("")
Application("Connection_String")
'Call the stored procedure to increment a counter on the page
"exec sp_AddHit"
No parameters, no return, no error handling, that's it
2. A call with parameters
"exec sp_AddHit '', 1"
Please note that the split parameters are not returned to records.
3. Return to the record
Dim objConn
Dim objRs
Set objConn = ("")
Set objRs = ("")
Application("Connection_String")
'Call the stored procedure to increment a counter on the page
objConn, "exec sp_ListArticles '1/15/2001'"
'Loop through recordset and display each article
4、……
Dim objConn
Dim objCmd
'Instantiate objects
Set objConn = ("")
set objCmd = ("")
Application("ConnectionString")
With objCmd
.ActiveConnection = conn 'You can also just specify a connection string here
.CommandText = "sp_InsertArticle"
.CommandType = adCmdStoredProc 'Requires the file or typelib meta tag
'Add Input Parameters
. .CreateParameter("@columnist_id", adDouble, adParamInput, , columnist_id)
. .CreateParameter("@url", adVarChar, adParamInput, 255, url)
. .CreateParameter("@title", adVarChar, adParamInput, 99, url)
. .CreateParameter("@description", adLongVarChar, _
adParamInput, 2147483647, description)
'Add Output Parameters
. .CreateParameter("@link_id", adInteger, adParamOutput, , 0)
'Execute the function
'If not returning a recordset, use the adExecuteNoRecords parameter option
.Execute, , adExecuteNoRecords
link_id = .Parameters("@link_id")
End With
5. Code of stored procedure
Create PROCEDURE dbo.sp_InsertArticle
(
@columnist_id int,
@url varchar(255),
@title varchar(99),
@description text
@link_id int OUTPUT
)
AS
BEGIN
INSERT INTO dbo.t_link (columnist_id,url,title,description)
VALUES (@columnist_id,@url,@title,@description)
SELECT @link_id = @@IDENTITY
END
Several ways to call stored procedures with parameters
Author: China Forum Network Collection Source: http:// Joining time: 2004-8-25
Author: Beggar Cat
Several ways to call stored procedures with parameters
Recently, many friends have asked questions about calling stored procedures. Here are a brief introduction to several methods of ASP calling stored procedures with parameters.
1 This is also the easiest method, with two input parameters, no return value:
set connection = ("")
someDSN
"procname varvalue1, varvalue2"
''Clear all objects into nothing, free up resources
set connection = nothing
2. If you want to return the Recordset set:
set connection = ("")
someDSN
set rs = ("")
"Exec procname varvalue1, varvalue2",connection
''Clear all objects into nothing, free up resources
set rs = nothing
set connection = nothing
3. Neither of the above two methods can have a return value (except Recordset). If you want to get the return value, you need to use the Command method.
First of all, there are two types of return values. One is to directly return a value in the stored procedure, just like the functions of C and VB return values; the other is to return multiple values,
The variable names that store these values need to be specified first in the call parameter.
This example will deal with multiple parameters, input parameters, output parameters, return recordsets and a direct return value (is it all enough?)
The stored procedure is as follows:
use pubs
GO
-- Establish a stored procedure
create procedure sp_PubsTest
-- Define three parameter variables, note the third one, the special mark is used for output
@au_lname varchar (20),
@intID int,
@intIDOut int OUTPUT
AS
SELECT @intIDOut = @intID + 1
SELECT *
FROM authors
WHERE au_lname LIKE @au_lname + ''%''
--Return a value directly
RETURN @intID + 2
The asp program that calls the stored procedure is as follows:
<%@ Language=VBScript %>
<%
Dim CmdSP
Dim adoRS
Dim adCmdSPStoredProc
Dim adParamReturnValue
Dim adParaminput
Dim adParamOutput
Dim adInteger
Dim iVal
Dim oVal
Dim adoField
Dim adVarChar
‘These values are predefined constants in VB and can be called directly, but there is no predefined in VBScript.
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200
iVal = 5
oVal = 3
''Create a command object
set CmdSP = ("")
''Create a link
= "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
''Define command object call name
= "sp_PubsTest"
''Set command call type is a stored procedure (adCmdSPStoredProc = 4)
= adCmdSPStoredProc
''Add parameters to the command object
''Define the stored procedure has a direct return value, and is an integer, the default value is 4
("RETURN_VALUE", adInteger, adParamReturnValue, 4)
''Define a character input parameter
("@au_lname", adVarChar, adParaminput, 20, "M")
''Define an integer input parameter
("@intID", adInteger, adParamInput, , iVal)
''Define an integer output parameter
("@intIDOut", adInteger, adParamOutput, oVal)
''Run the stored procedure and get the return record set
Set adoRS =
''Print out each record, the fields in it are virtual, you can ignore it
While Not
for each adoField in
& "=" & & "<br>" & vbCRLF
Next
"<br>"
Wend
''Print two output values:
"<p>@intIDOut = “ & ("@intIDOut").Value & "</p>"
"<p>Return value = " & ("RETURN_VALUE").Value & "</p>"
''Big cleaning
Set adoRS = nothing
Set = nothing
Set CmdSP = nothing
%>
There are other ways, which are a little bit off, and I will talk about it later
This article has referenced many articles, and will not be listed here.
Using stored procedures in Asp
In order to improve the efficiency of Asp programs, sometimes it is necessary to use the storage technology using Sql Server in Asp. Here is a brief introduction.
Establishment of stored procedures
Here is a brief introduction to how to establish stored procedures in the enterprise manager of Sql Server:
(1) Open Enterprise Manager
(2) Select the server group (SQL Server Group), server, database (Database) and the corresponding database, right-click the Stored Procdures item under the corresponding database, select New Stored Procedure in the pop-up menu, and enter the statement to establish a stored procedure in Stored Procedures Properties. Here is an example:
CREATE PROCEDURE proctest @mycola Char(10),@mycolb Char(10),@mycolc textAS
Insert into chatdata (mycola,mycolb,mycolc) values(@mycola,@mycolb,@mycolc)
In the document of Sql Server, its syntax is:
CREATE PROC[EDURE] procedure_name [;number] [
{@parameter data_type} [VARYING] [= default] [OUTPUT] ]
[,...n][WITH { RECOMPILE | ENCRYPTION
| RECOMPILE, ENCRYPTION }][FOR REPLICATION]AS
sql_statement [...n]
If you are not familiar with Sql syntax, you can use Check Syntax to check the syntax. In the above example, it means that the stored procedure is created with a stored procedure named mycola and 3 parameters. The first parameter mycola data type is char and width 10; the second parameter data type is char and width 10, and the third parameter data type is text. Here is the data type of Sql Server.
After the stored procedure is established, the following is how to call the stored procedure in the Asp program: Call the stored procedure in Asp. In order to improve the efficiency of the Asp program, sometimes it is necessary to use the storage technology using Sql Server in Asp. Here is a simple way to make a statement with parameters added above ("@mycolc", 201, 1, 250), the format is:
("parameter name", type, direction, size)
The meaning of the type of the parameter value is as follows:
Name value, integer value, function
adDBTimeStamp 135 Date and time data type
adDecimal 14 Decimal integer value
adDouble 5 Double precision decimal value
adError 10 System error message
AdGUID 72 Globally unique identifier (Globally unique identifier)
adDispath 9 COM/OLE Automatic Object (Automation Object)
adInteger 3 4-byte signed integer
adIUnknown 13 COM/OLE object
adLongVarBinary 205 Large 2-byte value
adLongVarChar 201 Large string value
adLongVarWChar 203 Large unencoded string
adNumeric 131 Decimal integer value
adSingle 4 Single-precision floating point decimal
adSmallInt 2 2-byte signed integer
adTinyInt 16 1 byte signed integer
adUnsignedBigInt 21 8-byte unsigned integer
adUnsignedInt 19 4-byte unsigned integer
adUnsignedSmallInt 18 2-byte unsigned integer
adUnsignedTinyInt 17 1 byte unsigned integer
adUserDefined 132 User-defined data type
adVariant 12 OLE object
adVarBinary 204 Double byte character variable value
adVarChar 200 character variable value
advarchar 202 Unencoded string variable value
adWchar 130 Uncoded string
The meaning of direction values is as follows:
Name value, integer value, function
adParamInput 1 Allow data to be entered into this parameter
adParamOutput 2 Allows data to be output to this parameter
adParamInputOutput 3 Allows data input and output to this parameter
adparamReturnValue 4 Allows data to return from a subroutine to this parameter
For more detailed resources, please refer to the documentation of Sql Server and IIS documentation resources.
<%
set conn=("")
set cmd=("")
strconn="dsn=pubs;uid=sa;pwd"
strconn
set =conn
="{call nono}"
'set rs= or
set rs=()
%>
2. A stored procedure for input parameters
<%
set conn=("")
set cmd=("")
strconn="dsn=pubs;uid=sa;pwd"
strconn
set =conn
="{call oneinput(?)}"
("@aaa",adInteger ,adParamInput )
cmd("@aaa")=100
()
%>
3. An input parameter and an output parameter
<%
set conn=("")
set cmd=("")
strconn="dsn=pubs;uid=sa;pwd"
strconn
set =conn
= "{call oneinout(?,?)}"
("@aaa",adInteger,adParamInput)
cmd("@aaa")=10
("@bbb",adInteger,adParamOutput)
()
bbb=cmd("@bbb")
%>
4. One input parameter, one output parameter, and one return value
<%
set conn=("")
set cmd=("")
strconn="dsn=pubs;uid=sa;pwd"
strconn
set =conn
="{?=call onereturn(?,?)}"
("@return_value",adInteger,adParamReturnValue )
("@aaa",adInteger,adParamInput )
cmd("@aaa")=10
("@bbb",adInteger,adParamOutput)
()
bbb=cmd("@bbb")
rrr=cmd("@return_value")
%>
How to call SQL stored procedure in ASP
2002-2-7 Jiaxiang365
<%set connection1 = ("")
... 'Join
set command1=("")
set =connection1
=4
="sp_1" 'SP name
(1)=... 'Parameter value
(2)=...
set recordset1=()
%>
Tips for ASP calling stored procedures
1. The simplest one is as follows
Dim objConn
Set objConn = ("")
Application("Connection_String")
'Call the stored procedure to increment a counter on the page
"exec sp_AddHit"
No parameters, no return, no error handling, that's it
2. A call with parameters
"exec sp_AddHit '', 1"
Please note that the split parameters are not returned to records.
3. Return to the record
Dim objConn
Dim objRs
Set objConn = ("")
Set objRs = ("")
Application("Connection_String")
'Call the stored procedure to increment a counter on the page
objConn, "exec sp_ListArticles '1/15/2001'"
'Loop through recordset and display each article
4、……
Dim objConn
Dim objCmd
'Instantiate objects
Set objConn = ("")
set objCmd = ("")
Application("ConnectionString")
With objCmd
.ActiveConnection = conn 'You can also just specify a connection string here
.CommandText = "sp_InsertArticle"
.CommandType = adCmdStoredProc 'Requires the file or typelib meta tag
'Add Input Parameters
. .CreateParameter("@columnist_id", adDouble, adParamInput, , columnist_id)
. .CreateParameter("@url", adVarChar, adParamInput, 255, url)
. .CreateParameter("@title", adVarChar, adParamInput, 99, url)
. .CreateParameter("@description", adLongVarChar, _
adParamInput, 2147483647, description)
'Add Output Parameters
. .CreateParameter("@link_id", adInteger, adParamOutput, , 0)
'Execute the function
'If not returning a recordset, use the adExecuteNoRecords parameter option
.Execute, , adExecuteNoRecords
link_id = .Parameters("@link_id")
End With
5. Code of stored procedure
Create PROCEDURE dbo.sp_InsertArticle
(
@columnist_id int,
@url varchar(255),
@title varchar(99),
@description text
@link_id int OUTPUT
)
AS
BEGIN
INSERT INTO dbo.t_link (columnist_id,url,title,description)
VALUES (@columnist_id,@url,@title,@description)
SELECT @link_id = @@IDENTITY
END
Several ways to call stored procedures with parameters
Author: China Forum Network Collection Source: http:// Joining time: 2004-8-25
Author: Beggar Cat
Several ways to call stored procedures with parameters
Recently, many friends have asked questions about calling stored procedures. Here are a brief introduction to several methods of ASP calling stored procedures with parameters.
1 This is also the easiest method, with two input parameters, no return value:
set connection = ("")
someDSN
"procname varvalue1, varvalue2"
''Clear all objects into nothing, free up resources
set connection = nothing
2. If you want to return the Recordset set:
set connection = ("")
someDSN
set rs = ("")
"Exec procname varvalue1, varvalue2",connection
''Clear all objects into nothing, free up resources
set rs = nothing
set connection = nothing
3. Neither of the above two methods can have a return value (except Recordset). If you want to get the return value, you need to use the Command method.
First of all, there are two types of return values. One is to directly return a value in the stored procedure, just like the functions of C and VB return values; the other is to return multiple values,
The variable names that store these values need to be specified first in the call parameter.
This example will deal with multiple parameters, input parameters, output parameters, return recordsets and a direct return value (is it all enough?)
The stored procedure is as follows:
use pubs
GO
-- Establish a stored procedure
create procedure sp_PubsTest
-- Define three parameter variables, note the third one, the special mark is used for output
@au_lname varchar (20),
@intID int,
@intIDOut int OUTPUT
AS
SELECT @intIDOut = @intID + 1
SELECT *
FROM authors
WHERE au_lname LIKE @au_lname + ''%''
--Return a value directly
RETURN @intID + 2
The asp program that calls the stored procedure is as follows:
<%@ Language=VBScript %>
<%
Dim CmdSP
Dim adoRS
Dim adCmdSPStoredProc
Dim adParamReturnValue
Dim adParaminput
Dim adParamOutput
Dim adInteger
Dim iVal
Dim oVal
Dim adoField
Dim adVarChar
‘These values are predefined constants in VB and can be called directly, but there is no predefined in VBScript.
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200
iVal = 5
oVal = 3
''Create a command object
set CmdSP = ("")
''Create a link
= "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
''Define command object call name
= "sp_PubsTest"
''Set command call type is a stored procedure (adCmdSPStoredProc = 4)
= adCmdSPStoredProc
''Add parameters to the command object
''Define the stored procedure has a direct return value, and is an integer, the default value is 4
("RETURN_VALUE", adInteger, adParamReturnValue, 4)
''Define a character input parameter
("@au_lname", adVarChar, adParaminput, 20, "M")
''Define an integer input parameter
("@intID", adInteger, adParamInput, , iVal)
''Define an integer output parameter
("@intIDOut", adInteger, adParamOutput, oVal)
''Run the stored procedure and get the return record set
Set adoRS =
''Print out each record, the fields in it are virtual, you can ignore it
While Not
for each adoField in
& "=" & & "<br>" & vbCRLF
Next
"<br>"
Wend
''Print two output values:
"<p>@intIDOut = “ & ("@intIDOut").Value & "</p>"
"<p>Return value = " & ("RETURN_VALUE").Value & "</p>"
''Big cleaning
Set adoRS = nothing
Set = nothing
Set CmdSP = nothing
%>
There are other ways, which are a little bit off, and I will talk about it later
This article has referenced many articles, and will not be listed here.
Using stored procedures in Asp
In order to improve the efficiency of Asp programs, sometimes it is necessary to use the storage technology using Sql Server in Asp. Here is a brief introduction.
Establishment of stored procedures
Here is a brief introduction to how to establish stored procedures in the enterprise manager of Sql Server:
(1) Open Enterprise Manager
(2) Select the server group (SQL Server Group), server, database (Database) and the corresponding database, right-click the Stored Procdures item under the corresponding database, select New Stored Procedure in the pop-up menu, and enter the statement to establish a stored procedure in Stored Procedures Properties. Here is an example:
CREATE PROCEDURE proctest @mycola Char(10),@mycolb Char(10),@mycolc textAS
Insert into chatdata (mycola,mycolb,mycolc) values(@mycola,@mycolb,@mycolc)
In the document of Sql Server, its syntax is:
CREATE PROC[EDURE] procedure_name [;number] [
{@parameter data_type} [VARYING] [= default] [OUTPUT] ]
[,...n][WITH { RECOMPILE | ENCRYPTION
| RECOMPILE, ENCRYPTION }][FOR REPLICATION]AS
sql_statement [...n]
If you are not familiar with Sql syntax, you can use Check Syntax to check the syntax. In the above example, it means that the stored procedure is created with a stored procedure named mycola and 3 parameters. The first parameter mycola data type is char and width 10; the second parameter data type is char and width 10, and the third parameter data type is text. Here is the data type of Sql Server.
After the stored procedure is established, the following is how to call the stored procedure in the Asp program: Call the stored procedure in Asp. In order to improve the efficiency of the Asp program, sometimes it is necessary to use the storage technology using Sql Server in Asp. Here is a simple way to make a statement with parameters added above ("@mycolc", 201, 1, 250), the format is:
("parameter name", type, direction, size)
The meaning of the type of the parameter value is as follows:
Name value, integer value, function
adDBTimeStamp 135 Date and time data type
adDecimal 14 Decimal integer value
adDouble 5 Double precision decimal value
adError 10 System error message
AdGUID 72 Globally unique identifier (Globally unique identifier)
adDispath 9 COM/OLE Automatic Object (Automation Object)
adInteger 3 4-byte signed integer
adIUnknown 13 COM/OLE object
adLongVarBinary 205 Large 2-byte value
adLongVarChar 201 Large string value
adLongVarWChar 203 Large unencoded string
adNumeric 131 Decimal integer value
adSingle 4 Single-precision floating point decimal
adSmallInt 2 2-byte signed integer
adTinyInt 16 1 byte signed integer
adUnsignedBigInt 21 8-byte unsigned integer
adUnsignedInt 19 4-byte unsigned integer
adUnsignedSmallInt 18 2-byte unsigned integer
adUnsignedTinyInt 17 1 byte unsigned integer
adUserDefined 132 User-defined data type
adVariant 12 OLE object
adVarBinary 204 Double byte character variable value
adVarChar 200 character variable value
advarchar 202 Unencoded string variable value
adWchar 130 Uncoded string
The meaning of direction values is as follows:
Name value, integer value, function
adParamInput 1 Allow data to be entered into this parameter
adParamOutput 2 Allows data to be output to this parameter
adParamInputOutput 3 Allows data input and output to this parameter
adparamReturnValue 4 Allows data to return from a subroutine to this parameter
For more detailed resources, please refer to the documentation of Sql Server and IIS documentation resources.