SQL SERVER Stored Procedure syntax

I. Defining variables-simple assignment declare @a intset @a=5 print @a-assigning values using SELECT statements declare @user1 nvarchar select @user1 = ' Zhang San ' print @user1 D eclare @user2 nvarchar () Select @user2 = Name from St_user where id=1 print @user2--Use the UPDATE statement to assign a value declare @user3 NVARC Har (update st_user set @user3 = Name where id=1 print @user3 Two, table, temp table, table variable--Create a temporary table 1 creation Table #DU_User1 ([ID] [i NT] NOT NULL, [Oid] [int] is not NULL, [Login] [nvarchar] () is not NULL, [RTX] [nvarchar] (4) is not NULL, [N AME] [nvarchar] (5) NOT NULL, [Password] [nvarchar] (max) NULL, [state] [nvarchar] (8) is not NULL); --Inserting a record into the temp table 1 insert INTO #DU_User1 (id,oid,[login],rtx,name,[password],state) VALUES (100,2, ' LS ', ' 0000 ', ' temporary ', ' 321 ',   ' Special '); --query data from St_user, populate to newly generated temporary table select * into #DU_User2 from St_user where id<8--query and Union two temporary tables select * from #DU_User2 where I D<3 Union SELECT * FROM #DU_User1-delete the two Temporary tables drop table #DU_User1 drop table #DU_User2-Create a temporary table for creating table #t ([I D] [int] not NULL, [Oid] [int.] Not NULL, [Login] [nvarchar] () not NULL, [RTX] [nvarchar] (4) is not NULL, [Name] [nvarchar] (5) is not NULL, [Pass Word] [nvarchar] (max) NULL, [state] [nvarchar] (8) not NULL,)--Insert the query result set (multiple data) into the temporary table insert into #t select * from St_us Er--cannot be inserted like this--select * into #t from dbo. St_user--Add a column for the int type self-growing sub-segment ALTER TABLE #t add [myid] int not NULL identity (top)--Add a column, default fill global unique identity ALTER TABLE #t add [myi D1] uniqueidentifier not NULL default (NEWID ()) SELECT * from #t drop table #t--Add self-growing column to query result set--when no primary key: Select IDENTITY (in t,1,1) as ID, Name,[login],[password] into #t from St_user select * from #t--when there is a primary key: Select (select SUM (1) from St_user WH Ere id<= a.id) as myid,* from St_user a order by myid--define table variable declare @t table (ID int not null, msg nvarchar (50 ) insert into @t values (1, ' 1 ') insert to @t values (2, ' 2 ') SELECT * from @t three, loop--while loop calculation 1 to 100 and declare @a intde Clare @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 INTD eclare @week nvarchar (3) Set @today =3 set @week =case when @today =1 and ' Monday ' when @today =2 and Tuesday ' when @toda Y=3 then ' Wednesday ' when @today =4 and Thursday ' when @today the =5 then ' Friday ' when @today =6 then ' Saturday ' when @today =7 then '  Sunday ' Else ' value error ' EndPrint @week V, cursor declare @ID intdeclare @Oid intdeclare @Login varchar (50)--Define a cursor declare user_cur  Cursor for select Id,oid,[login] from St_user--opens cursor open user_cur while @ @fetch_status =0 begin--reads cursor FETCH NEXT FROM User_cur into @ID, @Oid, @Login print @ID--print @Login endclose user_cur--Destroy cursor deallocate user_cur VI, temporary in trigger trigger Table: Inserted store data after insert and update operations Deleted hold data before delete and update operation--CREATE TRIGGER trigger user_onupdate on S T_user for Update as declare @msg nvarchar [email protected] record modification of select @msg = N ' name from "' + delet Ed. Name + N ' "modified to" ' + InsErted. Name + ' ' from inserted,deleted--insert Log table inserts into [log] (MSG) VALUES (@msg)--delete trigger drop trigger USER_ONUPDA Te Vii. stored procedure-Creating a stored procedure with output parameters create PROCEDURE pr_sum @a int, @b int, @sum int Outputasbegin set @[email  Protected][email protected] END--Creates a return value stored procedure create PROCEDURE pr_sum2 @a int, @b intasbegin return @ [Email protected] END-executes stored procedure get output return value declare @mysum intexecute pr_sum, @mysum outputprint @mysum-Execute stored procedure get return value Declar E @mysum2 intexecute @mysum2 = pr_sum2 print @mysum2 viii. categories of custom Function functions: 1) scalar value function 2) Table-valued function A: inline table-valued Function B: multi-language Sentence table Value function 3) system function--new scalar value function Create function func_sum1 (@a int, @b int) returns Intasbegin return @[email p Rotected] End--new inline table-valued function Create function func_usertab_1 (@myId int) returns Tableasreturn (SELECT * FROM St_user whe Re id< @myId)--New multi-statement table-valued Function Create function func_usertab_2 (@myId int) returns @t table ([ID] [int] not NULL, [Oid] [int] NOT NULL, [Login] [nvarchar] (a) not NULL, [RTX] [nvarchar] (4) is not NULL, [Name] [nvarchar] (5) Not NULL, [Password] [nvarchar] (max) NULL, [state] [nvarchar] (8) isn't null) asbegin INSERT INTO @t SELECT * From St_user where id< @myId returnend--Call table-valued Function SELECT * FROM dbo. Func_usertab_1 (15)--Call the scalar value function declare @s intset @s=dbo. FUNC_SUM1 (100,50) Print @s--delete scalar value functions drop function Func_sum1 Talk about the difference between a custom function and a stored procedure: first, Custom Function: 1. You can return table variable 2. There are a lot of restrictions, including the inability to use the output parameter, the temporary table, the operation within the function cannot affect the external environment, the result set cannot be returned by the Select, Update,delete, database table; 3. You must return a scalar value or a table variable custom function is generally used in a high degree of reuse, simple function, a place of strong contention. Second, the stored procedure 1. Table Variable 2 cannot be returned. With fewer restrictions, you can perform operations on a database table and return to DataSet 3.　　 You can return a scalar value, or you can omit the return stored procedure, which is generally used to implement complex functions and data manipulation.

　　

SQL SERVER Stored Procedure syntax