1.What is a stored procedure?
The stored procedure is compiled once and can be run multiple times (the stored procedure is stored in the server). It is a pre-compiled collection and runs quickly.
2. Commonly used system stored procedures
code
-- Purpose: Commonly used system stored procedures
EXEC sp_databases --List the databases in the current system
EXEC sp_renamedb 'test','test1'--Change database name (single user access)
USE stuDB
GO
EXEC sp_tables --List of objects queried in the current database
EXEC sp_columns stuInfo --returns information about a table column
EXEC sp_help stuInfo --View table stuInfo information
EXEC sp_helpconstraint stuInfo --View the constraints of table stuInfo
EXEC sp_helpindex stuMarks --View the index of table stuMarks
EXEC sp_helptext 'view_stuInfo_stuMarks' --View the statement text of the view
EXEC sp_stored_procedures --returns the list of stored procedures in the current database
use master
go
exec xp_cmdshell 'mkdir D:bank',no_output--Create folder
3. Custom stored procedures
(1.) Stored procedure without parameters
code
use studb
go
if exists(select * from sysobjects where name='proc_stu')
drop proc proc_stu
go
create procedure proc_stu
AS
DECLARE @writtenAvg float,@labAvg float --Average score variable of written test and computer test
SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks
print 'Average score in written test:'+convert(varchar(5),@writtenAvg)
print 'Average score of computer test:'+convert(varchar(5),@labAvg)
IF (@writtenAvg>70 AND @labAvg>70)
print 'Exam results of this class: Excellent'
ELSE
print 'Exam results of this class: poor'
print '------------------------------------------------ --'
print 'Students who failed to pass this exam:'
SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<60 OR labExam<60
GO
exec proc_stu --Execute stored procedures (2.) Stored procedures with input parameters
code
USE stuDB
GO
/*---Detect whether it exists: the stored procedure is stored in the system table sysobjects---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
DROP PROCEDURE proc_stu
GO
/*---Create stored procedure----*/
CREATE PROCEDURE proc_stu
@writtenPass int,
@labPass int-- You can add a default value so that the execution can be like this exec proc_stu without specifying parameters.
AS
print 'Written passing mark:'+convert(varchar(5),@writtenPass)
print 'Computer test passing line:'+convert(varchar(5),@labPass)
print '------------------------------------------------ --'
print 'Students who failed to pass this exam:'
SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass OR labExam<@labPass
GO
exec proc_stu 60,55
exec proc_stu 60,@labPass=55
exec proc_stu @writtenPass=60,@labPass=55
exec proc_stu @writtenPass=60,55--This line will report an error: parameter 2 must be passed, and subsequent parameters must be passed in the form of '@name = value'.
-- Once the '@name = value' form is used, all subsequent parameters must be passed in the '@name = value' form
(3.) Stored procedure with output parameters
code
USE stuDB
GO
/*---Detect whether it exists: the stored procedure is stored in the system table sysobjects---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
DROP PROCEDURE proc_stu
GO
/*---Create stored procedure----*/
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, --OUTPUT keyword, otherwise it is regarded as an input parameter
@writtenPass int=60, --The default parameters are put after
@labPass int=60 --The default parameters are put after
AS
print 'Written passing mark:'+convert(varchar(5),@writtenPass)
+ 'Computer-based test passing line:'+convert(varchar(5),@labPass)
print '------------------------------------------------ --'
print 'Students who failed to pass this exam:'
SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass OR labExam<@labPass
/*--Count and return the number of students who failed the exam--*/
SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
WHERE writtenExam<@writtenPass OR labExam<@labPass
GO
/*---Call stored procedure----*/
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64 --The passing mark for the computer test adopts the default value: the passing mark for the written test is 64 points, and the passing mark for the computer test is 60 points.
print '------------------------------------------------ --'
IF @sum>=3
print 'Number of people who failed:'+convert(varchar(5),@sum)+ 'People, more than 60%, the passing score should be lowered'
ELSE
print 'Number of people who failed:'+convert(varchar(5),@sum)+ 'People, has been controlled below 60%, and the passing score is moderate'
GO