SQL Server stored procedure syntax detailed _mssql

A stored procedure is one or more SQL commands that are stored as executable objects in the database.
Definitions are always abstract. A stored procedure is actually a set of SQL statements that can do something, except that the set of statements is in the database (here we talk about SQL Server). If we create stored procedures and call stored procedures in ASP, we can avoid mixing SQL statements with ASP code. There are at least three benefits to doing this:
First, greatly improve efficiency. The stored procedure itself executes very quickly, and invoking a stored procedure can significantly reduce the number of interactions with the database.
Second, improve security. If the SQL statement mixed in the ASP code, once the code compromised, but also means that the library structure compromised.
Third, to facilitate the reuse of SQL statements.

In ASP, the stored procedure is usually invoked through the command object, and the other invocation methods are described in this article according to different situations. To facilitate the description, according to the input and output of the stored procedure, make the following simple categories:
1. A stored procedure that returns only a single recordset
The following stored procedures are assumed (the purpose of this article is not to tell the T-SQL syntax, so the stored procedure gives the code only, not the description):

Copy Code code as follows:

/*sp1*/
CREATE PROCEDURE Dbo.getuserlist
As
SET NOCOUNT ON
Begin
SELECT * FROM dbo. [UserInfo]
End
Go

The above stored procedure gets all the records in the UserInfo table and returns a recordset. The ASP code that invokes the stored procedure through the Command object is as follows:

Copy Code code as follows:

' * * Call stored procedure via command object * *
DIM Mycomm,myrst
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Getuserlist" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
Set Myrst = Mycomm.execute
Set Mycomm = Nothing

The recordset obtained by the stored procedure is assigned to Myrst, and then the Myrst can be manipulated.
In the above code, the CommandType property indicates the type of the request, and the value and description are as follows:
-1 indicates that the type of the CommandText parameter cannot be determined

1 indicates that CommandText is a generic command type
2 indicates that the CommandText parameter is a table name that exists
4 indicates that the CommandText parameter is the name of a stored procedure

You can also invoke stored procedures by connection objects or Recordset objects, respectively, by using the following methods:

Copy Code code as follows:

' * * Invoke stored procedure via connection object * *
DIM Myconn,myrst
Set myconn = Server.CreateObject ("ADODB. Connection ")
MyConn.Open Myconstr ' myconstr is a database connection string
Set Myrst = Myconn.execute ("Getuserlist", 0,4) ' last argument meaning same as CommandType
Set myconn = Nothing

' * * Call stored procedure via Recordset object * *
DIM Myrst
Set Myrst = Server.CreateObject ("ADODB. Recordset ")
Myrst.open "Getuserlist", myconstr,0,1,4
' Myconstr is a database connection string, and the last argument has the same meaning as CommandType

2. Stored procedures without input and output
Please see the following stored procedures:

Copy Code code as follows:

/*sp2*/
CREATE PROCEDURE Dbo.deluserall
As
SET NOCOUNT ON
Begin
Delete FROM dbo. [UserInfo]
End
Go

The stored procedure deletes all records in the UserInfo table, without any input or output, and the invocation method is essentially the same as the above, except that the recordset is not obtained:

Copy Code code as follows:

' * * Call stored procedure via command object * *
DIM Mycomm
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Deluserall" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
Mycomm.execute ' There's no need to get the recordset here again

Set Mycomm = Nothing

This type of stored procedure can, of course, be invoked by connection object or Recordset object, although the Recordset object is created to get the recordset, or to use the Command object if no Recordset is returned.

3. Stored procedures with return values
When doing something like SP2, you should take full advantage of SQL Server's powerful transaction processing capabilities to maintain data consistency. Also, we may need the stored procedure to return to execution, and to this end, modify the SP2 as follows:

Copy Code code as follows:

/*sp3*/
CREATE PROCEDURE Dbo.deluserall
As
SET NOCOUNT ON
Begin
BEGIN TRANSACTION
Delete FROM dbo. [UserInfo]
IF @ @error =0
Begin
COMMIT TRANSACTION
Return 1
End
ELSE
Begin
ROLLBACK TRANSACTION
return 0
End
Return
End
Go

The stored procedure above returns 1 when the delete is executed smoothly, otherwise it returns 0 and rolls back the operation. In order to get the return value in ASP, you need to use the Parameters collection to declare the parameter:

Copy Code code as follows:

' * * Call stored procedure with return value and get return value * *
DIM Mycomm,mypara
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Deluserall" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
' Declare return value
Set Mypara = Mycomm.createparameter ("Return", 2,4)

MyComm.Parameters.Append Mypara
Mycomm.execute
' Get the return value
DIM RetValue
RetValue = Mycomm (0) ' or RetValue = mycomm.parameters (0)
Set Mycomm = Nothing

In Mycomm.createparameter ("return", 2,4), the meanings of each parameter are as follows:
The first argument ("Reture") is the name of the parameter. Parameter names can be set arbitrarily, but generally should be the same as the name of the parameter declared in the stored procedure. Here is the return value, which I am accustomed to set to "Reture";
The second parameter (2) indicates the data type of the parameter, the specific type code refer to the ADO reference, and the following are the commonly used type codes:

Copy Code code as follows:

adbigint:20;
adbinary:128;
Adboolean:11;
adchar:129;
addbtimestamp:135;
adempty:0;
Adinteger:3;
Adsmallint:2;
adtinyint:16;
advarchar:200;

For the return value, only the shape is taken, and-1 to-99 is the reserved value;
The third argument (4) indicates the property of the parameter, where 4 indicates that it is a return value. The value of this parameter is described as follows:
0: type cannot be determined; 1: input parameters; 2: input parameters; 3: input or output parameters; 4: return value

The above mentioned ASP code, should be said to be complete code, that is, the most complex code, in fact

Copy Code code as follows:

Set Mypara = Mycomm.createparameter ("Return", 2,4)
MyComm.Parameters.Append Mypara

can be simplified to

Copy Code code as follows:

MyComm.Parameters.Append Mycomm.createparameter ("return", 2,4)

It can even continue to be simplified and will be explained later.
For stored procedures with parameters, it can only be invoked using the Command object (there is also data that can be invoked through the Connection object or Recordset object, but I haven't tried it).

4. Stored procedures with input parameters and output parameters
The return value is actually a special output parameter. In most cases, we use stored procedures that have both input and output parameters, such as the user name of an ID user in the User Information table, an input parameter----user ID, and an output parameter----user name. The stored procedures for implementing this feature are as follows:

Copy Code code as follows:

/*sp4*/
CREATE PROCEDURE Dbo.getusername
@UserID int,
@UserName varchar () output
As
SET NOCOUNT ON
Begin
If @UserID is null return
Select @UserName =username
FROM dbo. [UserInfo]