SqlServer存储过程的创建与使用

declare @d int
set @d = 1
IF @d = 1
BEGIN
PRINT '正确'
END
ELSE BEGIN
PRINT '错误'
END

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 @i INT
SET @i = 1
WHILE @i<1000000 BEGIN
set @i=@i+1
END

DECLARE @cur1 CURSOR FOR SELECT .........OPEN @cur1
FETCH NEXT FROM @cur1 INTO 变量
WHILE(@@FETCH_STATUS=0)
BEGIN
处理.....
FETCH NEXT FROM @cur1 INTO 变量
END
CLOSE @cur1
DEALLOCATE @cur1

--表重命名
exec sp_rename 'stu', 'stud';--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

--创建一个返回结果集的存储过程（proc或者procedure均可）
if (object_id('proc_get_student', 'P') is not null)--判断存储过程是否存在 另外一种 if (exists (select * from sys.objects where name = 'proc_get_student'))//
drop proc proc_get_student --删除存储过程
go
create proc proc_get_student --创建存储过程
as
select * from student; --结果集--调用执行存储过程，得到返回集(exec或者execute均可)
exec proc_get_student;

--修改存储过程
alter proc proc_get_student
as
select * from student; --修改后的SQL语句--调用执行存储过程，得到返回集(exec或者execute均可)
exec proc_get_student;

--创建一个返回结果集的存储过程（proc或者procedure均可）
if (object_id('proc_find_stu', 'P') is not null)--判断存储过程是否存在
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)--两个参数
as
select * from student where id between @startId and @endId --查询语句
go--调用执行存储过程，2,4为参数
exec proc_find_stu 2, 4;

--创建一个返回结果集的存储过程（proc或者procedure均可）
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go--调用执行存储过程
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

--创建一个返回结果集的存储过程（proc或者procedure均可）
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默认输入参数
@name varchar(20) out, --输出参数
@age varchar(20) output--输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go--调用执行存储过程
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name '#' @temp;