SQLSERVER存储过程基本语法

--定义变量
declare @a int;
--赋值
set @a = 123456;
--输出
print @a;

--使用select语句赋值
declare @student nvarchar(50);
select @student = Sname from student where sid = 1001;
print @student

--使用update语句赋值
declare @user nvarchar(50);
update student set @user=sname where sid = 1002;
print @user

select * from student;

--更新语句
declare @user2 nvarchar(50);
set @user2 = 'dddd'
update student set sname = @user2 where sid = 1002;
print @user2

select * from student;

--创建临时表
create table #users(
	[uid] [int] not null,
	[uname] [nvarchar](50) not null,
	[uage] [int] null
);
--向临时表插入一条数据
insert into #users(uid,uname) values(1003,'zizi');
select * from #users;

insert into #users(uid,uname) values(1,'11');
insert into #users(uid,uname) values(2,'22');
insert into #users(uid,uname) values(3,'33');
insert into #users(uid,uname) values(4,'44');
insert into #users(uid,uname) values(5,'55');
insert into #users(uid,uname) values(6,'66');
insert into #users(uid,uname) values(7,'77');

create table #users2(
	[uid] [int] not null,
	[uname] [nvarchar](50) not null,
	[uage] [int] null
);
insert into #users2 values(1008,'lll',20);
--注意：从#users表中查询出数据保存到#users3中，#users3不用自己创建
select * into #users3 from #users where uid<5;
select * from #users3;

--查询并联合两个临时表
select * from #users where uid<5 union select * from #users2
select * from #users where uid<5 union select * from student

--删除临时表
drop table #users;
drop table #users2;
drop table #users3;

CREATE TABLE #t(
       [EmployeeID] [int] NOT NULL, -- 员工ID
       [EmployeeName] [nvarchar](50) NULL, --员工姓名
       [OTHours] [int] NULL, --这天加班小时数
       [OTDate] [date] NULL, --加班日期
       [PayPerHour] [int] NULL --每小时的加班费
) ON [PRIMARY]
--将查询结果集（多条数据）插入临时表
insert into #t select * from OTList;
select * from #t;
--另一种方式
select * into #tt from OTList;
select * from #tt

--添加一列，为int型自增长字段，从1开始递增，每次自增1
alter table #t add [myid] int not null identity(1,1)

--添加一列，默认填充全球唯一标识
alter table #t add [myid1] uniqueidentifier not null default(newid())
--newid() 创建类型uniqueidentifier的唯一值。

drop table #t;
drop table #tt;


--给查询结果集增加自增长列
--无主键时
select identity(int,1,1) as id,[sname],[sage] into #t from student;
select * from #t;

--有主键时
select (select sum(1) from OTList where EmployeeID <= o.EmployeeID)as myID,* from OTList o order by myID;
select * from OTList;

--定义表变量
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 @i int
declare @sum int
set @i=1
set @sum=0
while @i<=100
begin
    set @sum+=@i
    set @i+=1
end
print @sum


--if,else条件分支
if(1+1=2)
begin
    print 'right'
end
else
begin
    print 'wrong'
end

--when then条件分支
declare @today int
declare @week nvarchar(20)
set @today=3
set @week=case
    when @today=1 then 'Monday'
    when @today=2 then 'Tuesday'
    when @today=3 then 'Wednesday'
    when @today=4 then 'Thursday'
    when @today=5 then 'Friday'
    when @today=6 then 'Saturday'
    when @today=7 then 'Sunday'
    else 'error'
end
print @week

--创建带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
/*
自定义函数
　　函数的分类：
　　　　1）标量值函数
　　　　2）表值函数
　　　　　　　　a:内联表值函数
　	　　　　　　b:多语句表值函数
　　　　3）系统函数
　*/　

--新建标量值函数
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