SQL Server stored procedure Basic syntax

First, define variables

--Simple assignment  declare @a intset @a=5 print @a   --Assigning values using SELECT statements  declare @user1 nvarchar (50)  select @user1 = ' Zhang San ' print @user1  declare @user2 nvarchar  select @user2 = Name from St_user where ID=1&N Bsp;print @user2    --Use the UPDATE statement to assign a value  declare @user3 nvarchar  update st_user Set @user3 = Name where Id=1 print @user3    II, table, temp table, table variable  --create temp table 1 create table #DU_User1   (      &NBSP;[ID] [int]  not null,      [oid] [int] not null,      [lo GIN] [nvarchar] (a) not null,     &NBSP;[RTX] [nvarchar] (4) Not null,      [name] [NVA Rchar] (5) Not null,      [password] [nvarchar] (max) null,      [state] [ NVARCHAR] (8) not NULL);  --inserts a record into temporary table 1  insert into #DU_User1 (id,oid,[login],rtx,name,[password],state) VALUES (100,2, ' LS ', ' 0000 ', ' temporary ', ' 321 ', ' special ');    --from St_userQuery data, populate to the newly generated temporary table  select * into #DU_User2 from St_user where id<8   --queries and unites two temporary tables  select * from # Du_user2 WHERE id<3 Union SELECT * FROM #DU_User1    --Delete two temporary tables  drop table #DU_User1  drop table #DU_User2  --Create a temporary table  create table #t   (     [ID] [int] not null,     [Oid] [int] ] Not null,     [Login] [nvarchar] (NO) null,     [RTX] [nvarchar] (4) Not Null, &nbs P   [Name] [nvarchar] (5) Not null,     [Password] [nvarchar] (max) null,     [State] [NVA Rchar] (8) not null, )    --Insert query result set (multiple data) into temporary table  insert into #t select * from st_user -- You cannot insert  --select * into #t from dbo. st_user   --adds a column for the int type self-growing sub-segment  alter table #t add [myid] int not NULL IDENTITY ( --) to add a column, Default padding globally unique identifier  alter table #t add [myid1] uniqueidentifier not NULL default (NEWID ())    select * from #t &N Bsp;drOP table #t--adding self-growing columns to the query result set    --No primary key:  select IDENTITY (int,1,1) as ID, Name,[login],[password] into # T from St_user select * from #t    --with primary key:  select (select SUM (1) from St_user where id<= a.id) As myid,* from St_user a order by myid--define table variable  declare @t table (     ID int not null,   &nbsp ; msg nvarchar (+) null)  insert into @t values (1, ' 1 ')  insert to @t values (2, ' 2 ')  select * from @t  III, follow Ring  --while Cycle calculation 1 to 100 and  declare @a intdeclare @sum intset @a=1 set @sum =0 while @a<=100  begin    Set @[email protected]     set @a+=1 endprint @sum four, conditional statement  --if, Else Conditional Branch  if (1+1=2)  begin    print ' to ' endelsebegin    print ' wrong ' end  --when Then conditional branch  declare @today intdeclare @week nvarchar (3)  set @today =3 set @week =case    when @ Today=1 then ' Monday '     when @today =2 then ' Tuesday '    When @today =3 and Wednesday '     when @today =4 then ' Thursday '     when @today =5 then ' Friday '     W Hen @today =6 Then ' Saturday '     when @today =7 then ' Sunday '     Else ' value error ' EndPrint @week    five, cursor  d Eclare @ID intdeclare @Oid intdeclare @Login varchar ()    --define a cursor  declare user_cur cursor FOR Select Id,oid,[login] from st_user --open cursor  open user_cur while @ @fetch_status =0 begin--Read cursor      FETCH NEXT from User_cur to @ID, @Oid, @Login      print @ID     --print　　@Login  endclose user_cur --Destroy cursors  deallocate user_cur vi. temporary tables in triggers   triggers:  inserted  Store data after insert and update operations   deleted  storage for data before delete and update  --CREATE TRIGGER  create trigger User_onupdate      on St_user      for update as     declare @msg nvarchar ( &) nbsp   [email protected] Record changes    &NBsp Select @msg = N ' name changed from ' + Deleted.name + n ' to ' + Inserted.name + ' ' from inserted,deleted    --Insert Log table &nbs p;    INSERT INTO [LOG] (MSG) VALUES (@msg)        --Delete trigger  drop trigger User_ OnUpdate vii. stored procedures  --creating stored procedures with output parameters  create PROCEDURE pr_sum     @a int,     @b int,     @sum int outputasbegin    SET @[email protected][email protected]  end  --Create return value stored procedure  create PROCEDURE pr_sum2     @a int,     @b intasbegin    return @[email protected] end      --execute stored procedure get output return value   Declare @mysum intexecute pr_sum, @mysum outputprint @mysum    --Execute stored procedure get return value  declare @ mysum2 Intexecute @mysum2 = pr_sum2 1,2 print @mysum2      viii. Classification of custom functions   functions:  1) Scalar-valued functions &N　　　　Bsp 2) Table-valued functions   A: Inline table-valued functions   B: Multi-statement table-valued functions  　　　　3) System functions    --new scalar value functions  create function func_sum1  (     @a int,     @b I NT)  returns intasbegin    return @[email protected] end  --new inline table-valued function  create function func_usertab_1  (     @myId int)  returns Tableasreturn (SELECT * from St_user where ID&L t; @myId)    --new multi-statement table-valued functions  create function func_usertab_2  (     @myId int)  returns @t Table (     [ID] [int] not null,     [Oid] [int.] not null,   &nbsp ; [Login] [nvarchar] () not null,     [Rtx] [nvarchar] (4) Not null,     [Name] [nvarchar] (5) Not NULL,&NBSP;&NB Sp   [Password] [nvarchar] (max) null,     [state] [nvarchar] (8) not NULL)  asbegin    INSERT INTO @t select * from St_user where id< @myId      returnend  --Call table-valued function  select * FROM Dbo. Func_usertab_1 (&N)bsp;--calls a scalar-valued function  declare @s intset @s=dbo. FUNC_SUM1 (100,50)  print @s   --Delete scalar value functions  drop function func_sum1 talking about the difference between a custom function and a stored procedure:  I. Custom Function:  1. You can return table variables   2. There are a lot of restrictions, including   cannot use the output parameter;  cannot be used with temporary tables;  functions inside the function cannot affect the external environment;  cannot return a result set through select;  cannot Update,del ETE, database table;  3. Must return a scalar value or a table variable   Custom function is generally used in a high degree of reuse, simple function, a strong fight against the place.   Storage procedure   1. Cannot return table variable   2. With fewer restrictions, you can perform operations on database tables, and you can return datasets   3. Can return a scalar value, or omit return  stored procedures are generally used in the implementation of complex functions, data manipulation.

SQL Server stored procedure basic syntax