Example learning MSSQL stored procedure analysis _mssql

Example 1: Querying a student table in a database through a stored procedure
We know that in SQL, we query a table, which can be queried through the select * from student, what should we write in the stored procedure?
Solution:
First we open the Query Analyzer (the stored procedures in the example below are created using the query manager);
Then we'll create a stored procedure for later use (as if we were programming a function first):
CREATE PROCEDURE Proc_stu
As
SELECT * FROM Student
Go
So a stored procedure is created, and now we're going to perform
We can enter in Query Analyzer: Execute Proc_stu can see the effect
To analyze the above example, Proc_stu is the name of the stored procedure, and the SELECT * from student is clearly the SQL statement, and we only need the execute (execute) stored procedure name to execute it. of which, procedure and execute can be abbreviated to proc and exec respectively

The above demonstrates a very simple stored procedure. Let's take a look at the stored procedure with parameters
Example 2. Query the records of SNO (student) as ' s1 ' in the table
Create proc Proc_stu
@ Ssno varchar (10)
As
SELECT * FROM student where Sno = @ Ssno
Go
So a stored procedure with parameters is OK, "@ variable name" is the method used in SQL to represent user-defined parameters, and some friends may have seen "@@ 变量 name", which is the system itself. That means the variable name is system-defined and cannot be arbitrarily altered. After this analysis, I believe you should understand it.
To execute a stored procedure with parameters, you need to use the following statement: Exec proc_stu S1 can also not write parameters, but in this case, you must first create a stored procedure with the parameter assignment, you can assign null, otherwise, the system will report an error.

Finally, let's talk about how to use a stored procedure to return a value:
Example 3. Return to student table number of secondary school students
Method One: (use external variable output)
Create proc Proc_stu
@ num INT output--annotated as an external variable
As
SELECT @ num = count (*) from student
Go
Now we're going to do the following:
To use an external variable, we first declare: DECLARE @ return we use this variable to receive the external variables in the stored procedure
Execute proc_stu, @ num = @ return output
So we get the return value, and now we're going to show this return value with the following assignment statement
Select ' Return ' = @ return

Method two (using return):
Note: Return returns only cosmetic data
Create proc Proc_stu
@ num INT
As
SELECT @ num = count (*) from student
return @ num
Go
We're going to execute the following:
DECLARE @ return
EXEC @ return = Proc_stu
Select ' Return ' = @ return