SQL Server Stored Procedures

The concept of stored procedures

Stored procedure procedure is a set of SQL statements that are compiled to complete a particular function, stored in a database, and executed by specifying the name of the stored procedure and giving parameters.

Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets, and return values.

Because stored procedures are compiled on the database server and stored in the database when they are created, the stored procedure runs faster than a single SQL statement block. At the same time, because it only needs to provide the stored procedure name and the necessary parameter information in the call, it can reduce the network traffic and the simple network burden to some extent.

--=========== System stored procedure ==============--Display System Databaseexecsp_databases--Show Database Detailsexecsp_helpdb--Replace name for the specified databaseexecSp_renamedb'AA','BB'--View details for a specified table nameexecsp_help Student--View creation text information for a specified index, view, stored procedure, and so onexecsp_helptext sp_help--the calling stored procedure must be the first bit in the batch fileif exists(Select *  fromsysobjectswhereName= 'Table1')    Drop TableTable1Gosp_help sp_help--========== System extended stored procedure ================ UseMasterGo--Create a folder bankexecxp_cmdshell'mkdir D:\bank', No_outputif exists(Select *  fromsysdatabaseswhereName= 'BANKDB')        Drop DatabaseBANKDBGo        Create DatabaseBANKDB on Primary(Name= 'BANKDB', filename= 'D:\bank\bankDB.mdf', size=5MB, MaxSize=10MB, FileGrowth=  the%        )Log  on(Name= 'Bankdb_log', filename= 'D:\bank\bankDB_log.ldf', size=5MB, FileGrowth=  the%    )    --call the stored procedure to view folder informationexecxp_cmdshell'dir D:\bank\'--======== Create a stored procedure, query Java logic for the last Test average and the list of learners who failed the exam ========= UseMySchoolGoif exists(Select *  fromsysobjectswhereName= 'Sp_getavgresult')Drop procSp_getavgresultGo--Create a stored procedure implementationCreate procSp_getavgresult@returnnum intOutput--return of the number of failed persons    @returnsum intOutput--total number of exams taken    @subjectName varchar( -),--Add an account    @score int =  - --Add input parameter (passing score) asDeclare @subjectId intDeclare @maxdateDateDeclare @avg int--Query the number of the Java Logic courseSelect @subjectId =Subjectid fromSubjectwhereSubjectname= @subjectName--Query Java Logic course last Test timeSelect @maxdate = MAX(examdate) fromResultwhereSubjectid= @subjectId--query the average score of the last exam for the Java Logic courseSelect @avg = AVG(Studentresult) fromResultwhereSubjectid= @subjectId  andExamdate= @maxdatePrint 'List of people who failed the exam: ======================='--Query Java Logic Course list of students who failed the last examSelectStudentname,studentresult fromStudent SInner JoinResult R onR.studentno=S.studentnowhereSubjectid= @subjectId          andExamdate= @maxdate         andStudentresult< @score        --total number of people who took the examSelect @returnsum = COUNT(*) fromResultwhereExamdate= @maxdate  andSubjectid= @subjectId--number of persons who have failed to inquireSelect @returnnum = COUNT(*) fromResultwhereExamdate= @maxdate  andSubjectid= @subjectId                                   andStudentresult< @score        if(@avg >  -)begin    Print 'Exam Result: excellent'EndElsebegin    Print 'exam results: Poor'EndGo--======= calls the stored procedure to implement the business logic ===========----Declare @sum int --total number of exams takenDeclare @num int --number of people who have failedDeclare @percent float(2)--percent passed--call a stored procedureexecSp_getavgresult@numOutput@sumOutput@subjectName = 'Java Logic',@score =  -Print '========================================='Print 'the number of participants in the exam is:' + Convert(varchar( -),@sum)Print 'the number of failed persons is:' + Convert(varchar( -),@num)--Calculate pass RateSet @percent = Convert(float(2),(@sum - @num))/@sum *  -Print 'percent of passing:' + Convert(varchar( -),@percent)+'%'--determine if you want to adjust the passif(@percent >  -)begin    Print 'no need to adjust the bar ... 'EndElsebegin    Print 'need to lower the fraction ... 'End

SQL Server Stored Procedures