SQL Server stored procedure Basic syntax

first, define variables--simple Assignment Declare @aintSet@a=5Print @a--Use the SELECT statement to assign a value declare @user1 nvarchar ( -) Select@user1 ='Zhang San'print @user1 declare @user2 nvarchar ( -) Select@user2 = Name fromSt_userwhereId=1Print @user2--use the UPDATE statement to assign a value declare @user3 nvarchar ( -) Update St_userSet@user3 = NamewhereId=1Print @user3
Second, table, temporary table, table variable--Creating a temporary table 1 create TABLE #DU_User1 ([ID] [int] Not NULL, [Oid] [int] Not NULL, [Login] [nvarchar] ( -) not NULL, [Rtx] [nvarchar] (4) not NULL, [Name] [nvarchar] (5) not NULL, [Password] [nvarchar] (max) is null, [state] [nvarchar] (8) not NULL); --insert a record into temporary table 1 insert into #DU_User1 (id,oid,[login],rtx,name,[password],state) VALUES ( -,2,'LS','0000','Temporary','321','Special'); --querying data from St_user, populating to a newly generated temporary tableSelect* Into #DU_User2 fromSt_userwhereid<8--querying and federating two temporary tablesSelect* from#DU_User2whereid<3UnionSelect* from#DU_User1--Delete two temporary tables drop table #DU_User1 drop table #DU_User2--Create a temporary table #t created table ([ID] [int] Not NULL, [Oid] [int] Not NULL, [Login] [nvarchar] ( -) not NULL, [Rtx] [nvarchar] (4) not NULL, [Name] [nvarchar] (5) not NULL, [Password] [nvarchar] (max) is null, [state] [nvarchar] (8) not NULL,)--Insert a query result set (multiple data) into a temporary table insert into #tSelect* fromSt_user--cannot be inserted like this--Select* Into #t fromdbo. St_user--add a column for the int type self-growing sub-segment ALTER TABLE #t add [myID]intNot NULL IDENTITY (1,1) --add a column, default fill global unique identity ALTER TABLE #t add [myid1] uniqueidentifier not NULLdefault(NEWID ())Select* from#t drop table #t--increase the self-growth column for the query result set--when there is no primary key:SelectIDENTITY (int,1,1) asID, Name,[login],[password] into #t fromSt_userSelect* from#t--when there is a primary key:Select(SelectSUM (1) fromSt_userwhereid<= a.id) asmyid,* fromSt_user A ORDER by MyID--Define table variable declare @t table (IDintNotNULL, msg nvarchar ( -)NULL) insert into @t values (1,'1') insert into @t values (2,'2') Select* from@t
Third, circulation--While Loop calculates 1 to 100 and declare @aintDeclare @sumintSet@a=1 Set@sum =0  while@a<= -beginSet@sum + =@aSet@a+=1EndPrint @sum
Iv. conditional Statements--if, Else conditional branchif(1+1=2) BeginPrint'the'EndElseBeginPrint'wrong'End--When and then conditional branch DECLARE @todayintdeclare @week nvarchar (3) Set@today =3 Set@week = CaseWhen @today=1Then'Monday'When @today=2Then'Tuesday'When @today=3Then'Wednesday'When @today=4Then'Thursday'When @today=5Then'Friday'When @today=6Then'Saturday'When @today=7Then'Sunday'Else 'Value Error'EndPrint @week
V. CURSOR DECLARE @IDintDeclare @Oidintdeclare @Login varchar ( -) --defines a cursor declare user_cur for SelectId,oid,[login] fromSt_user--opens the cursor open user_cur while@ @fetch_status =0begin--read cursor FETCH next fromuser_cur into @ID, @Oid, @Login print @ID--print @Login endclose user_cur--Destroying Cursors deallocate user_cur
Vi. temporary tables in trigger triggers: Inserted store data after insert and update operations Deleted hold data before delete and update operations--creating a trigger Create trigger user_onupdate on St_user forUpdate as declare @msg nvarchar ( -) --@msg Record ChangesSelect@msg = N'name from "'+ Deleted.name + N'"Modify to"'+ Inserted.name +'"'  frominserted,deleted--Insert Log table insert into [log] (MSG) VALUES (@msg)--Delete trigger drop trigger User_onupdate
Vii. Stored Procedures--create a stored procedure with an output parameter create PROCEDURE pr_sum @aint, @bint, @sumintOutputasbeginSet@[email protected]+@b END--creating a return value stored procedure create PROCEDURE pr_sum2 @aint, @bintAsbeginreturn @a+@b END--execute stored procedure get output return value declare @mysumintExecute Pr_sum1,2, @mysum outputprint @mysum--executes the stored procedure to get the return value of declare @mysum2intExecute @mysum2= Pr_sum21,2Print @mysum2 Eight, the classification of custom function functions:1) Scalar-valued functions2) Table-valued function A: inline table-valued Function B: Multi-statement table-valued function3) System Functions--new Scalar-valued function Create function func_sum1 (@aint, @bint) returnsint asbeginreturn@a+@b End--new Inline table-valued function Create function func_usertab_1 (@myIdint) returns table asreturn(Select* fromSt_userwhereid<@myId)--new multi-statement table-valued Function Create function func_usertab_2 (@myIdint) returns @t table ([ID] [int] Not NULL, [Oid] [int] Not NULL, [Login] [nvarchar] ( -) not NULL, [Rtx] [nvarchar] (4) not NULL, [Name] [nvarchar] (5) not NULL, [Password] [nvarchar] (max) is null, [state] [nvarchar] (8) not NULL) asBegininsert into @tSelect* fromSt_userwhereid<@myIdreturnEnd--calling table-valued functionsSelect* fromDbo. Func_usertab_1 ( the) --call a scalar-valued function declare @sintSet@s=dbo. FUNC_SUM1 ( -, -) Print @s--Delete a scalar-valued function func_sum1 The difference between a custom function and a stored procedure: one, a custom function:1. You can return table variables2There are a lot of restrictions, including the inability to use the output parameter, the temporary table, the internal operation of 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 strong fight against the place. Second, the stored procedure1. Cannot return table variable2less restrictive, can perform operations on database tables, can return datasets3. You can return a scalar value, or omit the return stored procedure is generally used in the implementation of complex functions, data manipulation.

Original from http://www.cnblogs.com/chaoa/articles/3894311.html

SQL Server stored procedure Basic syntax