SQLSERVER存储过程语法详解

--简单赋值 
declare @a  int
set @a=5 
print @a 
   
--使用select语句赋值 
declare @user1 nvarchar(50) 
select @user1= '张三'
print @user1 
declare @user2 nvarchar(50) 
select @user2 =  Name from ST_User  where ID=1 
print @user2 
   
--使用update语句赋值 
declare @user3 nvarchar(50) 
update ST_User  set @user3 =  Name where ID=1 
print @user3

--创建临时表1 
create table #DU_User1 
( 
      [ID] [ int ]   NOT NULL , 
      [Oid] [ int ]  NOT NULL , 
      [Login] [nvarchar](50)  NOT NULL , 
      [Rtx] [nvarchar](4)  NOT NULL , 
      [ Name ] [nvarchar](5)  NOT NULL , 
      [ Password ] [nvarchar]( max )  NULL , 
      [State] [nvarchar](8)  NOT NULL
); 
--向临时表1插入一条记录 
insert into #DU_User1 (ID,Oid,[Login],Rtx, Name ,[ Password ],State)  values (100,2, 'LS' ,'0000' , '临时' , '321' , '特殊' ); 
   
--从ST_User查询数据，填充至新生成的临时表 
select *  into #DU_User2  from ST_User  where ID<8 
   
--查询并联合两临时表 
select *  from #DU_User2  where ID<3  union select *  from #DU_User1 
   
--删除两临时表 
drop table #DU_User1 
drop table #DU_User2
 
--创建临时表 
CREATE TABLE #t 
( 
     [ID] [ int ]  NOT NULL , 
     [Oid] [ int ]  NOT NULL , 
     [Login] [nvarchar](50)  NOT NULL , 
     [Rtx] [nvarchar](4)  NOT NULL , 
     [ Name ] [nvarchar](5)  NOT NULL , 
     [ Password ] [nvarchar]( max )  NULL , 
     [State] [nvarchar](8)  NOT NULL , 
) 
   
--将查询结果集（多条数据）插入临时表 
insert into #t  select *  from ST_User 
--不能这样插入 
--select * into #t from dbo.ST_User 
   
--添加一列，为int型自增长子段 
alter table #t  add [myid]  int NOT NULL IDENTITY(1,1) 
--添加一列，默认填充全球唯一标识 
alter table #t  add [myid1] uniqueidentifier  NOT NULL default (newid()) 
   
select *  from #t 
drop table #t
--给查询结果集增加自增长列 
   
--无主键时： 
select IDENTITY( int ,1,1) as ID,  Name ,[Login],[ Password ]  into #t  from ST_User 
select *  from #t 
   
--有主键时： 
select ( select SUM (1)  from ST_User  where ID<= a.ID)  as myID,*  from ST_User a  order bymyID
--定义表变量 
declare @t  table
( 
     id  int not null , 
     msg nvarchar(50)  null
) 
insert into @t  values (1, '1' ) 
insert into @t  values (2, '2' ) 
select *  from @t

--while循环计算1到100的和 
declare @a  int
declare @ sum int
set @a=1 
set @ sum =0 
while @a<=100 
begin
     set @ sum +=@a 
     set @a+=1 
end
print @ sum

--if,else条件分支 
if(1+1=2) 
begin
     print  '对'
end
else
begin
     print  '错'
end
   
--when then条件分支 
declare @today  int
declare @week nvarchar(3) 
set @today=3 
set @week= case
     when @today=1  then '星期一'
     when @today=2  then '星期二'
     when @today=3  then '星期三'
     when @today=4  then '星期四'
     when @today=5  then '星期五'
     when @today=6  then '星期六'
     when @today=7  then '星期日'
     else '值错误'
end
print @week

declare @ID  int
declare @Oid  int
declare @Login  varchar (50) 
   
--定义一个游标 
declare user_cur  cursor for select ID,Oid,[Login]  from ST_User 
--打开游标 
open user_cur 
while @@fetch_status=0 
begin
--读取游标 
     fetch next from user_cur  into @ID,@Oid,@Login 
     print @ID 
     --print @Login 
end
close user_cur 
--摧毁游标 
deallocate user_cur

--创建触发器 
Create trigger User_OnUpdate  
     On ST_User  
     for Update  
As  
     declare @msg nvarchar(50) 
     --@msg记录修改情况 
     select @msg = N '姓名从“' + Deleted. Name + N '”修改为“' + Inserted. Name +  '”' fromInserted,Deleted 
     --插入日志表 
     insert into [LOG](MSG) values (@msg) 
       
--删除触发器 
drop trigger User_OnUpdate

--创建带output参数的存储过程 
CREATE PROCEDURE PR_Sum 
     @a  int , 
     @b  int , 
     @ sum int output
AS
BEGIN
     set @ sum =@a+@b 
END
   
--创建Return返回值存储过程 
CREATE PROCEDURE PR_Sum2 
     @a  int , 
     @b  int
AS
BEGIN
     Return @a+@b 
END
       
--执行存储过程获取output型返回值 
declare @mysum  int
execute PR_Sum 1,2,@mysum  output
print @mysum 
   
--执行存储过程获取Return型返回值 
declare @mysum2  int
execute @mysum2= PR_Sum2 1,2 
print @mysum2

--新建标量值函数 
create function FUNC_Sum1 
( 
     @a  int , 
     @b  int
) 
returns int
as
begin
     return @a+@b 
end
   
--新建内联表值函数 
create function FUNC_UserTab_1 
( 
     @myId  int
) 
returns table
as
return ( select *  from ST_User  where ID<@myId) 
   
--新建多语句表值函数 
create function FUNC_UserTab_2 
( 
     @myId  int
) 
returns @t  table
( 
     [ID] [ int ]  NOT NULL , 
     [Oid] [ int ]  NOT NULL , 
     [Login] [nvarchar](50)  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
   
--调用表值函数 
select *  from dbo.FUNC_UserTab_1(15) 
--调用标量值函数 
declare @s  int
set @s=dbo.FUNC_Sum1(100,50) 
print @s 
   
--删除标量值函数 
drop function FUNC_Sum1

print("Hello, World!")