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;