SQLSERVER2008 stored Procedure Basic syntax

First, define variables
--Simple assignment
DECLARE @a int
Set @a=5
Print @a

--Assigning values using the SELECT statement
declare @user1 nvarchar (50)
Select @user1 = ' Zhang San '
Print @user1
declare @user2 nvarchar (50)
Select @user2 = Name from St_user where id=1
Print @user2

--Use the UPDATE statement to assign a value
declare @user3 nvarchar (50)
Update St_user Set @user3 = Name where id=1
Print @user3

Second, table, temporary table, table variable
--Create 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) 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 (100,2, ' LS ', ' 0000 ', ' temporary ', ' 321 ', ' special ');

--query data from St_user, populate to the newly generated temporary table
SELECT * into #DU_User2 from St_user where id<8

--Query and unite 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] () not NULL,
[RTX] [nvarchar] (4) Not NULL,
[Name] [nvarchar] (5) Not NULL,
[Password] [nvarchar] (max) Null
[State] [nvarchar] (8) Not NULL,
)

--Inserting a query result set (multiple data) into a temporary table
INSERT INTO #t select * from St_user

--Add a column for the int type self-growing sub-segment
ALTER TABLE #t add [myid] int not NULL IDENTITY (up to)
--Add a column to populate the global unique identity by default
ALTER TABLE #t add [myid1] uniqueidentifier not NULL default (NEWID ())
SELECT * from #t
drop table #t

--Increase the self-growth column for the query result set
--when there is no primary key:
Select IDENTITY (int, +) 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 where id<= a.id) as myid,* from St_user a order by MyID
--Define Table variables
DECLARE @t table
(
ID int NOT NULL,
msg nvarchar (+) null
)
INSERT into @t values (1, ' 1 ')
INSERT into @t values (2, ' 2 ')
SELECT * FROM @t

Third, circulation
--while Cycle
DECLARE @a int
DECLARE @ sum int
Set @a=1
SET @ Sum =0
While @a<=100
Begin
SET @ Sum [email protected]
Set @a+=1
End
Print @ sum

Iv. conditional statements
--if,else Conditional Branch
if (1+1=2)
Begin
print ' Yes '
End
Else
Begin
print ' wrong '
End

--when then conditional branching
DECLARE @today int
declare @week nvarchar (3)
Set @today =3
Set @week = case
When @today =1 and then ' Monday '
When @today =2 and then ' Tuesday '
When @today =3 and then ' Wednesday '
When @today =4 and then ' Thursday '
When @today =5 and then ' Friday '
When @today =6 and then ' Saturday '
When @today =7 and then ' Sunday '
Else ' value error '
End
Print @week

Five, cursor
DECLARE @ID int
Declare @Oid int
declare @Login varchar ()

--defines 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
End
Close user_cur
--destroy cursor
deallocate user_cur

VI, Trigger
--create Trigger
"CREATE Trigger User_onupdate
on St_user
for Update
as
declare @msg nvarchar ()
[email protected] Record modification
Select @msg = N ' name from ' + Deleted. Name + N ' "changed to" ' + Inserted. Name + ' "' from inserted,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
@a int,
@b int,
@ Sum int output
As
BEGIN
SET @ Sum [email protected][email protected]
END

--Create return value stored procedure
CREATE PROCEDURE pr_sum2
@a int,
@b int
As
BEGIN
Return @[email protected]
END

--Execute stored procedure get output return value
DECLARE @mysum int
Execute pr_sum, @mysum output
Print @mysum

--Execute stored procedure get return value
DECLARE @mysum2 int
Execute @mysum2 = pr_sum2
Print @mysum2

Eight, custom functions
--New scalar value function
Create function Func_sum1
(
@a int,
@b int
)
returns int
As
Begin
return @[email protected]
End

--New inline table-valued function
Create function Func_usertab_1
(
@myId int
)
Returns table
As
Return (SELECT * from St_user where 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] () not NULL,
[RTX] [nvarchar] (4) Not NULL,
[Name] [nvarchar] (5) Not NULL,
[Password] [nvarchar] (max) Null
[State] [nvarchar] (8) Not NULL
)
As
Begin
INSERT INTO @t select * from St_user where id< @myId
Return
End

--Call table-valued function
SELECT * FROM dbo. Func_usertab_1 (15)
--Call scalar value function
DECLARE @s int
Set @s=dbo. FUNC_SUM1 (100,50)
Print @s

--delete scalar value functions
Drop function Func_sum1

SQLSERVER2008 stored Procedure Basic syntax