SQL server stored procedure

Time:2021-4-30

1、 Stored procedure

A stored procedure is a collection of precompiled SQL statements that are stored under a name and processed as a unit. Stored procedure replaces the traditional way of executing SQL statement one by one. A stored procedure can contain a series of SQL statements for query, insert, update, delete and other operations. When the stored procedure is called and executed, these operations are executed at the same time

Similar to other programming languages, stored procedure can accept input parameters and return multiple values to calling procedure or batch processing in the form of output parameters; Contains programming statements used to perform operations (including calling other procedures) in the database; Returns a status value to the calling procedure or batch to indicate success or failure (and the reason for the failure).

  1. Create stored procedure
create proc [EDURE] procedure_name [:number]
  [{@parameter data_type}
      [VARYING] [=default] [OUTPUT]
  ] [...n]
AS sql_statement
parameter describe
create procedure Keyword, can also be written as create proc
procedure_name The name of the stored procedure created
number Grouping stored procedures
@parameter Stored procedure parameters, which can declare one or more parameters
data_type All data types (including text, ntext and image) can be used as parameters of stored procedure, but cursor data type can only be used for output parameter
VARYING Optional, specifies the result set supported as an output parameter (dynamically constructed by a stored procedure, and the content can be changed). This keyword is only applicable to cursor parameters
default Optional, which means to set the default value for the parameter
OUTPUT Optional, indicating that the parameter is a return parameter, and the parameter value can be returned to the calling procedure
n Indicates that multiple parameters can be defined
AS Specifies the operation to be performed by the stored procedure
sql_statement Procedure bodies in stored procedures
--Stored procedure queries all data
--Begin... End is similar to {} in programming language
create proc stu1
as
begin
  select * from student;
end
go
 
exec stu1
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--The stored procedure queries the user information according to the conditional user name
create proc stu2
@Sname varchar (50) -- declare global variables
as
begin
  select * from student s where [email protected];
end
go
 
Exec stu2 'Wang Nan'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--Set the user name inside the stored procedure to query the user information
create proc stu3
@Sname varchar (50) ='wang Nan '
as 
begin
  select * from student s where [email protected]; 
end
go
 
exec stu3 
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--The stored procedure queries whether the user information exists according to the user name
create proc stu4
@sname varchar(50),
@Result varchar (8) output -- output parameter
as
begin
   if (select COUNT(1) from student s where [email protected])>0
   --if exists (select COUNT(1) from student s where [email protected])
   set 
    @Result ='exist! "
   else
   set 
    @Result ='does not exist! "
end
go
 
 
declare @result varchar(8)
Exec stu4 'Wang Nan 1' ,@result  output
print @result
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--Local variable user name is set inside stored procedure to query user information
create proc stu5
as
Declare @ sname varchar (50) -- local variable declaration
Set @ sname ='yang Mi '
begin
  select * from student s where [email protected]
end
go
 
exec stu5
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--The stored procedure queries the user name according to the student number of the conditional user
create proc stu6
@stuNo varchar(50)
as
declare @sname varchar(50)
set @sname=(select s.stuName from student s where [email protected])
select @sname
go
 
exec stu6 '01'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--Insert user information into stored procedure
create proc stu7
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5)
as 
begin
  insert into student 
    (stuNo,stuName,stuAge,stuSex)
  values 
    (@stuNo,@stuName,@stuAge,@stuSex) 
end
go
 
Exec stu7 '07','wang mang ','2000-9-9 9:9:9','female '
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--Stored procedures delete the corresponding user information according to the user name
[email protected]@Rowcount returns the number of operations
--Return returns information to terminate the following operation
create proc stu8
@stuName varchar(50)
as
begin
  delete from student where [email protected]
  return @@rowcount
end
go
 
declare @result varchar(50)
Exec @ result = stu8 'Wang Mang'
Select @ result as' number of deleted items'
--print @result
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--The stored procedure queries the average score of the user according to his student number
create proc stu9
@stuNo varchar(50),
@avg int output
as 
begin
  set @avg=(select AVG(courseScore) from course where [email protected])
  --Equivalent
  --select @avg=AVG(courseScore) from course where [email protected]
end
go
 
declare @avg int
exec stu9 '02',@avg output
print @avg
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--The stored procedure queries user information and course information according to the user's student number
create proc stu10
@stuNo varchar(50)
as
  select c.stuNo,s.stuName,s.stuAge,s.stuSex,c.courseName,c.courseScore from student s join course c on s.stuNo=c.stuNo where [email protected]o
go
 
exec stu10 '02'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--The stored procedure judges whether the student number exists or not, inserts the user information and returns the message; Exist, return information
create proc stu11
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5),
@result varchar(50) output
as
  if exists (select * from student where [email protected])
    begin
      Set @ result ='sorry, the student number already exists! "
    end    
  else
    begin
      insert into student 
        (stuNo,stuName,stuAge,stuSex)
      values 
        (@stuNo,@stuName,@stuAge,@stuSex)
      Set @ result ='congratulations, user information inserted successfully! "   
    end
go
 
declare @result varchar(50)
Exec stu11 '06','wang Zhonglei ','1980-8-8 8:9:0','male ' ,@result  output
print @result
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--The stored procedure queries the relationship between the current user's average score and the total average score
create proc stu12
@stuNo varchar(50)
as
declare @curAvg decimal(18,2)
declare @totalAvg decimal(18,2)
if exists(select * from course where [email protected])
  begin
    set @totalAvg=(select AVG(courseScore) from course)
    select @curAvg=AVG(courseScore) from course where [email protected]
    Print ('total average score: '+ convert (varchar (18) ,@totalAvg ))
    Print ('average score of the student: '+ convert (varchar (18) ,@curAvg ))
    if @curAvg>@totalAvg
      Print'above average
    else 
      Print'below average  
  end
else
  Print 'the corresponding score information of the student does not exist, please query again! "
go
 
exec stu12 '03'
go

SQL server stored procedure learning (easy to understand)_ Heroism – CSDN blog_ SQL server stored procedure

  1. Manage stored procedures

  2. Executing stored procedures

After a stored procedure is created, it can be executed through execute, which is abbreviated as exec

  • Grammatical format
[{exec|execute}]
  {
    [@return_status=]
    {module_name[;number]|@modlue_name_var}
    [[@parameter=]{value
                    |@variable[OUTPUT]
                    |[DEFAULT]
                    }
    ]
    [...n]
    [WITH RECOMPILE]
  }
  • Parameter description
parameter describe
@return_status Optional integer variable, the return status of the storage module. This variable must be declared in a batch, stored procedure, or function before the execute statement
module_name Is the fully or partially qualified name of the stored procedure or scalar valued user-defined function to call. The module name must conform to the identifier rule. The name of an extended stored procedure is always case sensitive, regardless of the server’s collation
number Is an optional integer used to group procedures with the same name. This parameter cannot be used for extended stored procedures
@module_name_var Is a locally defined variable name, representing the module name
@parameter module_ The parameter of name is the same as that defined in the module. The “@” symbol must be added before the parameter name
value The parameter value passed to the module or command. If the parameter name is not specified, the parameter value must be provided in the order defined in the module
@variable Is used to store parameters or return parameter variables
OUTPUT The specified module or command string returns a parameter, and the matching parameter in the module or command string must also be created with the keyword output. Use this keyword when using cursor variables as parameters
DEFAULT According to the definition of the module, the default value of the parameter is provided. When the parameter value required by the module has no default value defined and is missing or the default keyword is specified, an error will occur
WITH RECOMPILE After you specify a module, force the new plan to compile, use, and discard. If there is an existing query plan for the module, the plan will remain in the cache
  1. View stored procedures

  2. Using sys.sql_ Modules view the definition of the stored procedure

select * from sys.sql_modules
  1. Using object_ Definition view the definition of a stored procedure
-- object_ Id the stored procedure ID to view
select OBJECT_DEFINITION(object_id)
  1. Using SP_ Helptext view the definition of a stored procedure
sp_helptext 'proc_student'