Simple example of creating and invoking SQL Server stored procedures in a project

The use of SQL Server stored procedures can greatly improve the speed of the program, simplifying the difficulty of programming maintenance, has been widely used.
Create a stored procedure
As with a data table, you need to create a stored procedure before using it, and its concise syntax is:

Reference:
Create PROC Stored Procedure name
[Parameter list (multiple with "," delimited)]
As
SQL statements

Cases:

Reference:
Create PROC Upgetusername
@intUserId INT,
@ostrUserName NVARCHAR Output--parameters to be exported
As
BEGIN
--assigns the value of the uname to the @ostrUserName variable, which is the parameter to be output
Select @ostrUserName =uname from Uuser Where [email protected]
END

Where the Create PROC statement (the complete statement is create PROCEDURE) means to tell SQL SERVER, now you need to establish a stored procedure, Upgetusername is the name of the stored procedure, @intUserId and @ Ostrusername is the two parameters of the stored procedure, note that in SQL Server, all user-defined variables begin with "@", the OUTPUT keyword indicates that the parameter is for output, and as follows the contents of the stored procedure. As soon as the above code is executed in the Query Analyzer, SQL Server creates a stored procedure named "Upgetusername" in the current database. You can open Enterprise Manager, select the database for the current operation, and then select stored procedure in the tree list on the left, and you can see the stored procedure you just created in the list on the right (if not, refresh it).
Second, the call of the stored procedure

A stored procedure named "Upgetusername" was previously created, and the function of the stored procedure is literally understood to be used to obtain the name of a user. The stored procedure is set up, and the next step is to call it in the application, and take a look at the call in the ASP program.

Reference:
Dim Adocomm

'//Create an object that we use to call the stored procedure
Set Adocomm = CreateObject ("Adodb.command")
With Adocomm
'//Set the connection, and set the Adoconn as the connected ADODB. Connection Object
. ActiveConnection = Adoconn
'//type is stored procedure, adCmdStoredProc = 4
. CommandType = 4
'//Stored procedure name
. CommandText = "Upgetusername"
'//Set user number
. Parameters.item ("@intUserId"). Value = 1
'//Execute stored Procedure
. Execute
         
'//Get the user name returned from the stored procedure
Response.Write "User name:" &. Parameters.item ("@ostrUserName"). Value
End with
'//Release object
Set Adocomm = Nothing

With these two steps, you can already create and use simple stored procedures. Let's look at a slightly more complex stored procedure to learn more about the application of stored procedures.
Third, the practical application of the stored procedure

User login is often used in ASP projects, but using stored procedures to do validation may not be much, then do an example of writing a simple user login to verify the stored procedure.

Reference:
Create PROC Upuserlogin
@strLoginName NVARCHAR (20),
@strLoginPwd NVARCHAR (20),
@blnReturn BIT OUTPUT
As
--Define a variable that is temporarily used to save the password
DECLARE @strPwd NVARCHAR (20)
BEGIN
--Query The current user's password from the table, assign to the @strPwd variable, and compare him below
Select @strPwd =uloginpwd from Uuser Where [email protected]

IF @strLoginPwd = @strPwd
BEGIN
SET @blnReturn = 1
--Update user last login time
Update uuser SET ulastlogin=getdate () Where [email protected]
END
ELSE
SET @blnReturn = 0
END

The stored procedure for user logon is established. Note that if you have more than one statement within a region, you must use the BEGIN ... End keyword.
Reference:
Dim Adocomm
'//Create an object that we use to call the stored procedure
Set Adocomm = CreateObject ("Adodb.command")
With Adocomm
'//Set the connection, and set the Adoconn as the connected ADODB. Connection Object
. ActiveConnection = Adoconn
'//type is stored procedure, adCmdStoredProc = 4
. CommandType = 4
'//Stored procedure name
. CommandText = "Upuserlogin"
'//Set login name
. Parameters.item ("@strLoginName"). Value = "* * *"
'//Set login password
. Parameters.item ("@strLoginPwd"). Value = "123456"
'//Execute stored Procedure
. Execute
         
'//Determine if login is successful
If. Parameters.item ("@blnReturn"). Value = 1 Then
Response.Write "Congratulations, login success! "
Else
Response.Write "No, it seems wrong oh ... "
End If
End with
'//Release object
Set Adocomm = Nothing

Through the above steps, the simple user login verification process is done, and now as long as it is integrated into the program can be implemented simple user login verification, about the other details by yourself to deal with.
The two stored procedures described above return only one value, so let's look at a stored procedure that returns a recordset.

Reference:
Create PROC Upgetuserinfos
@intUserGroup INT
As
BEGIN
--extracting eligible data from a database
Select Uname,ugroup,ulastlogin from Uuser Where [email protected]
--Insert a column total
UNION
Select ' Total people: ', COUNT (Ugroup), NULL from Uuser Where [email protected]
END

Now let's look at the call of the ASP program.

Reference:
Dim Adocomm
Dim Adort
'//Create an object that we use to call the stored procedure
Set Adocomm = CreateObject ("Adodb.command")
Set adors = CreateObject ("ADODB. Recordset ")
With Adocomm
'//Set the connection, and set the Adoconn as the connected ADODB. Connection Object
. ActiveConnection = Adoconn
'//type is stored procedure, adCmdStoredProc = 4
. CommandType = 4
'//Stored procedure name
. CommandText = "Upgetuserinfos"
'//Set up user groups
. Parameters.item ("@intUserGroup"). Value = 1
'//execute stored procedures, unlike the above examples, the open method of using the recordset
Adors.open Adocomm
'//Show First value
Response.Write Adors.fields (0). Value
End with
'//Release object
Set adors = Nothing
Set Adocomm = Nothing

Finally, let's look at how SQL Server gets the data for the table returned by another stored procedure inside the stored procedure.

Assuming that the name of "Another stored procedure" is SP1, there are no parameters, the returned result set is 3 columns, all int, then "stored procedure" adds a temporary table or table variable with the same number of result set columns to receive the result set of "Another stored procedure"
As follows
CREATE PROCEDURE SP2
As
DECLARE @t Table (a int,b int,c int)

INSERT into @t (a,b,c)
EXEC SP1

SELECT * from @t

Simple example of creating and invoking SQL Server stored procedures in a project