SQL Server database stored procedures

Classification of stored procedures
(1) System stored procedure
The name of the system stored procedure usually begins with "sp_"
Created, managed, and used by SQL Server
stored in the resource database
Methods similar to those in the Java language class library
(2) Extended stored procedures
The name of an extended stored procedure usually begins with "Xp_"
Stand alone in DLL form
(3) User-defined stored procedures
Stored procedures that are created by the user in their own database
User-defined methods similar to those in the Java language

//Call the stored procedure syntax :
EXECUTE procedure name [parameter]
Or
EXEC procedure name [parameters]

To define the syntax for a stored procedure:
CREATE proc[edure] Stored procedure name
@ Parameter 1 Data type = default Value OUTPUT,
... ... ,
@ parameter n data type = default Value OUTPUT
As
SQL statements
GO

Example 1:
IF EXISTS (SELECT * from sysobjects WHERE name = ' Usp_grade_subject ')
DROP PROCEDURE Usp_grade_subject
GO
/*---Create a stored procedure----*/
CREATE PROCEDURE Usp_grade_subject
As
SELECT Gradename,subjectname,classhour from Grade
INNER JOIN Subject on Grade.gradeid=subject.gradeid
ORDER by Subject.gradeid,subjectno
GO
/*---Call Execute stored procedure---*/
EXEC Usp_grade_subject

Example 2 stored procedure with input parameters:
CREATE PROCEDURE Usp_unpass
@subName varchar (50),
@score int
As
DECLARE @date datetime--Recent exam times
DECLARE @subjectNo INT--Course number
SELECT @subjectNo =subjectno from Subject Where subjectname = @subName
... ...
SELECT Studentname,student.studentno,studentresult from Student
INNER JOIN Result on student.studentno = Result.studentno
WHERE Studentresult < @score and examdate = @date and [email protected]
GO

Example 3 creates a stored procedure with a parameter default value:

CREATE PROCEDURE Usp_unpass
@subName varchar (50),
@score int = 60
As
... ...
GO
Call a stored procedure with the default value for the parameter:
EXEC Usp_unpass ' C # OOP '--using default to represent defaults

Example 4 creates a stored procedure with an output parameter:

CREATE PROCEDURE Usp_query_num
@UnPassNum INT OUTPUT,
@TotalNum INT OUTPUT,
@SubjectName NCHAR (10),
@Pass INT = 60--It is recommended to put the default parameters last
As
DECLARE @date datetime
DECLARE @subjectNo int
... ...
SELECT @UnPassNum = COUNT (*) from Result WHERE [email protected] and [email protected] and studentresult< @Pass
SELECT @TotalNum = COUNT (*) from Result WHERE [email protected] and [email protected]
GO
Call a stored procedure with an output parameter:
DECLARE @UnPassNum int
DECLARE @TotalNum int
EXEC usp_query_num @UnPassNum output, @TotalNum output, ' Java Logic '
--must be called with the OUTPUT keyword, and the returned results will be stored in variables @unpassnum and @totalnum , respectively.

SQL Server database stored procedures