SQL Server database stored procedures

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