Before we say the stored procedure, let's start with some basic syntax for the SQL Server database
--0. Code blocks in SQL Server/*similar to the various development languages, SQL Server also has a block of code, most of the language code blocks are separated by curly braces ({})*/beginEnd--for code splitting in SQL Server databases--definition of variables, assignment, outputDeclare @a int--Defining VariablesSet @a=5 --assigning values to variablesPrint @a --Print the value of a variable --Assigning values using the SELECT statement--Select can be used for variable assignments or for output variables when the output variable is the same as in our query statementDeclare @user1 nvarchar( -) Select @user1='Zhang San'--Defining VariablesSelect @user1--Output Variables--Second, table, temporary table, table variable--Create a temporary tableCreate Table#DU_User1 ([ID] [int] not NULL, [Oid] [int] not NULL, ); --inserting records into a staging tableInsert into#DU_User1 (id,oid)Values( -,2); --Defining Table VariablesDeclare @t Table(IDint not NULL, msgnvarchar( -)NULL) ;--to insert a record into a table variableInsert into @t Table(id,msg)Values( -,'123'); --third, loop while loop for 1 to 100 and (only while loop in SQL Server)Declare @a intDeclare @sum intSet @a=1 Set @sum=0 while @a<= - begin Set @sum+=@a Set @a+=1 EndSelect @sum--Iv. Conditional Statements--if,else Conditional Branchif(1+1=2) begin Print 'the'EndElsebegin Print 'wrong'End --When and then conditional branchingDeclare @today intDeclare @week nvarchar(3) Set @today=3 Set @week= Case when @today=1 Then 'Monday' when @today=2 Then 'Tuesday' when @today=3 Then 'Wednesday' when @today=4 Then 'Thursday' when @today=5 Then 'Friday' when @today=6 Then 'Saturday' when @today=7 Then 'Sunday' Else 'Value Error'EndPrint @week--v. CursorsDeclare @ID intDeclare @Oid intDeclare @Login varchar( -) --define a cursorDeclareUser_curcursor for SelectId,oid,[Login] fromSt_user--Open CursorOpenUser_cur while @ @fetch_status=0 begin--reading Cursors Fetch Next fromUser_cur into @ID,@Oid,@Login Print @ID --Print @LoginEndCloseUser_cur--Destroying CursorsdeallocateUser_cur
Stored procedures in a SQL Server database can be divided into two types:
First, the system stored procedures
System stored procedures are system-created stored procedures designed to facilitate querying information from system tables or to complete administrative tasks or other system administration tasks related to updating database tables. System stored procedures are primarily stored in the master database and stored procedures that begin with an "SP" underscore. Although these system stored procedures are in the master database, we can still invoke system stored procedures in other databases. There are some system stored procedures that are automatically created in the current database when a new database is created.
--common system stored proceduresexecsp_databases;--View DatabaseexecSp_tables;--View TableexecSp_columns student;--View ColumnsexecSp_helpindex student;--View IndexexecSp_helpconstraint student;--ConstraintsexecSp_stored_procedures;execSp_helptext'Sp_stored_procedures';--View stored procedure creation, definition statementsexecsp_rename student, Stuinfo;--Modify the name of a table, index, columnexecSp_renamedb mytempdb, MyDB;--Change the database nameexecSp_defaultdb'Master','MyDB';--Change the default database for loginsexecsp_helpdb;--database Help, querying database informationexecsp_helpdb master;
II. user-defined stored procedures
A user-defined stored procedure is an object of one or more SQL statements that the user does not complete with some business or functionality, and a complex data operation. If you're working at the front desk. Multiple database connections may be involved. But if you use stored procedures. It's only once. There are advantages in response time.
Let's talk about user-defined stored procedures.
-- Create stored procedure syntax Create proc | procedure pro_name [{@ parameter} {parameter type}[Output], {@ parameter [output] as -- sql_statements
--Stored Procedure usage
Execute Pro_name[{@ parameter}[output],{@ parameter} [Output]]
--For example:--define a stored procedure called Proc_nameCreate procedureProc_name@a1 int,@p1 intOutput--variable p1 as output parameter asSelect @p1=Count(*) from Table whereId=@a1--use proc_name This stored procedureDeclare @a1 int,@p1 intSet @a1=1ExecuteProc_name@a1,@p1Output
SQL Server database stored procedures