Instance learning MSSQL Stored Procedure Analysis

Example 1: query the student table in the database through the Stored Procedure
We know that in SQL, when we query a table, we can use select * from student to query it. How should we write it in the stored procedure?
Solution:
First, open the query analyzer. (The stored procedures in the following example are created using the query manager );
Then we will create a stored procedure for future use (as if we compile a function in programming ):
Create procedure proc_stu
As
Select * from student
Go
This stored procedure has been created. Now let's execute it.
We can enter execute proc_stu in the query analyzer to see the effect.
Analyze the example above. proc_stu is the name of the stored procedure, and select * from student is obviously an SQL statement. During execution, we only need to execute the name of the stored procedure, you can. procedure and execute can be abbreviated as proc and exec respectively.

The above demonstrates a simple stored procedure. Let's take a look at the stored procedure with parameters.
Example 2: query the records with Sno (student ID) as 's1' in the student table.
Create proc proc_stu
@ Ssno varchar (10)
As
Select * from student where SnO = @ ssno
Go
In this way, the stored procedure with parameters is OK. "@ variable name" is the method used in SQL to represent user-defined parameters, some friends may also see "@ variable name", which is included in the system. that is to say, the variable name is defined by the system and cannot be changed randomly. after this analysis, I believe you can 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. However, in this case, you must first add a parameter value during the creation of the stored procedure, you can assign null, otherwise, the system reports an error.

Finally, let's talk about how to use the stored procedure to return a value:
Example 3. Return student table student count
Method 1: (use the external variable output)
Create proc proc_stu
@ Num int output -- indicate that it is an external variable
As
Select @ num = count (*) from student
Go
Now let's execute:
To use external variables, we must first declare: declare @ return. We use this variable to receive external variables in the stored procedure.
Execute proc_stu, @ num = @ return output
Then we get the returned value. Now we use the following value assignment statement to display the returned value.
Select 'Return '= @ return

method 2 (return):
note: return can only return Integer Data
Create proc proc_stu
@ num int
as
select @ num = count (*) from student
return @ num
go
Let's execute:
declare @ return
exec @ return = proc_stu
select 'Return '= @ return