????1?? ??????
????create proc | procedure pro_name
????[{@????????????} [=????] [output]??
????{@????????????} [=????] [output]??
????....
????]
????as
????SQL_statements
????2?? ?????????????洢????
????--?????洢????
????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 proc_get_student;
????3?? ???洢????
????--???洢????
????alter proc proc_get_student
????as
????select * from student;
????4?? ???δ洢????
????--???δ洢????
????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
????exec proc_find_stu 2?? 4;
????5?? ???????????洢????
????--???????????洢????
????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%';
????6?? ??????????洢????
????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;
????7?? ??????洢????
????--WITH RECOMPILE ??????
????if (object_id('proc_temp'?? 'P') is not null)
????drop proc proc_temp
????go
????create proc proc_temp
????with recompile
????as
????select * from student;
????go
????exec proc_temp;
????8?? ????洢????
????--????WITH ENCRYPTION
????if (object_id('proc_temp_encryption'?? 'P') is not null)
????drop proc proc_temp_encryption
????go
????create proc proc_temp_encryption
????with encryption
????as
????select * from student;
????go
????exec proc_temp_encryption;
????exec sp_helptext 'proc_temp';
????exec sp_helptext 'proc_temp_encryption';