CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]
[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS
[ begin ]
    T-SQL 語(yǔ)句
[ end ]

CREATE table MyStudentInfo
(
  Id int not null primary key,
  Name varchar(16),
  Age int,
  Gender varchar(2),
  Phone varchar(16),
  Address varchar(50),
  GradeId int,
  Score int
)

INSERT INTO MyStudentInfo
SELECT 1,'張三',20,'1','15801258912','上海',1,90 UNION
SELECT 2,'李四',22,'1','12345678901','北京',1,84 UNION
SELECT 3,'王五',16,'1','13976891234','天津',2,35 UNION
SELECT 4,'趙六',19,'1','18676891234','重慶',3,56 UNION
SELECT 5,'小紅',21,'2','17776891234','廣州',4,82 UNION
SELECT 6,'小王',25,'2','13176891234','深圳',5,54 UNION
SELECT 7,'小劉',18,'2','13374591234','南京',6,69 UNION
SELECT 8,'小張',16,'1','13974596734','長(zhǎng)沙',6,58 UNION
SELECT 9,'小羅',27,'1','13175122786','武漢',7,40 UNION
SELECT 10,'小袁',21,'2','17715872346','石家莊',8,34

CREATE table GradeInfo
(
  Id int not null primary key,
  GradeName varchar(16)
)

INSERT INTO GradeInfo
SELECT 1,'.NET' UNION
SELECT 2,'Android' UNION
SELECT 3,'PHP' UNION
SELECT 4,'UI' UNION
SELECT 5,'HTML5' UNION
SELECT 6,'JAVA' UNION
SELECT 9,'HADOOP' UNION
SELECT 10,'大數(shù)據(jù)'

-- 創(chuàng)建無(wú)參數(shù)的存儲(chǔ)過(guò)程，獲取MyStudentInfo表的所有記錄
create procedure Proc_GetInfo
as
begin
  select * from MyStudentInfo
end

-- 執(zhí)行存儲(chǔ)過(guò)程
exec Proc_GetInfo

 CREATE proc Proc_InsertData
 @Id int,
 @Name varchar(16),
 @Age int,
 @Gender varchar(2),
 @Phone varchar(16),
 @Address varchar(50),
 @GradeId int,
 @Score int
 as
 begin
  insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@Gender,@Score)
 end
 go

 exec Proc_InsertData @Id=11,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=62
 GO

 CREATE proc Proc_InsertDefault
 @Id int,
 @Name varchar(16),
 @Age int,
 @Gender varchar(2)='1',
 @Phone varchar(16),
 @Address varchar(50)='沈陽(yáng)',
 @GradeId int,
 @Score int
 as
 begin
  insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@Gender,@Score)
 end
 go

exec Proc_InsertDefault @Id=12,@Name='小阮',@Age=24,@Phone='13832757512',@GradeId=4,@Score=70
go

 create proc Proc_Out
 @Id int, -- 默認(rèn)為輸入?yún)?shù)
 @gradeId int out  -- 輸出參數(shù)
 as
 begin
  select @gradeId=GradeId from MyStudentInfo where id=@id
 end

 declare @GradeIdOut int
 exec Proc_Out 10,@GradeIdOut out
 print @GradeIdOut
 GO

DECLARE @Sid int
EXEC Proc_Out @gradeId=@Sid OUT,@Id=10
PRINT @Sid
go

 create proc Proc_Return
 @Id int,
 @Name varchar(16),
 @Age int,
 @Gender varchar(2),
 @Phone varchar(16),
 @Address varchar(50),
 @GradeId int,
 @Score int
 as
 begin
   IF exists (SELECT * FROM MyStudentInfo WHERE Id=@Id)
       begin
         print '要插入的學(xué)生的學(xué)號(hào)已經(jīng)存在'
         ROLLBACK
       end
   else
       begin
         insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@GradeId,@Score)
         print '恭喜，數(shù)據(jù)插入成功'
       end
 end
 GO

exec Proc_Return @Id=10,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=75
go

exec Proc_Return @Id=15,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=75
go

 CREATE proc Proc_Delete
 @name varchar(16)
 as
 begin
    delete FROM MyStudentInfo where Name=@name
    return @@rowcount
 end

  declare @Del_Row int
  exec @Del_Row= Proc_Delete @name='小劉'
  select @Del_Row 刪除的行
  GO

 create proc Proc_Var
 @id int,
 @ResStr varchar(16) out
 as
 begin
  --聲明變量
  declare @curAvg decimal(18,2)
  declare @totalAvg decimal(18,2)
  select @totalAvg=AVG(Score) from MyStudentInfo
  select @curAvg=AVG(Score) from MyStudentInfo where Id=@id
  IF @curAvg>@totalAvg
     set @ResStr='高于平均分'
  else
     set @ResStr='低于平均分'
  print '總平均分為:'+convert(varchar(18),@totalAvg)
  print '該生平均分為:'+convert(varchar(18),@curAvg)
  print @ResStr
 end

 declare @resstring varchar(20)
 exec Proc_Var 6,@resstring out
 go

 create proc Proc_OutPut
 @Id int,--默認(rèn)輸入?yún)?shù)
 @Score int out,--輸出參數(shù)
 @Age int output --輸入輸出參數(shù)
 as
   begin
     select @Score=Score,@Age=Age from MyStudentInfo where Id=@Id and Age=@Age
   end
go

declare @Sid int,@SScore int,@SAge int
set @Sid=8
set @SAge=16
exec Proc_OutPut @Sid,@SScore out,@SAge output
print '成績(jī):'+convert(varchar(8),@SScore)+',年齡:'+convert(varchar(8),@SAge)
go

create proc Proc_Page
@StartIndex int,
@EndIndex int
as
begin
   select COUNT(*) from MyStudentInfo
   select * from (
      SELECT ROW_NUMBER() OVER(ORDER BY Id) AS rowid,* FROM MyStudentInfo ) temp
   where temp.rowid between @StartIndex AND @EndIndex
end
go

exec Proc_Page 1,4
go

create proc Proc_P
@PageIndex int,
@PageSize int
as
begin
    declare @StartRow int,@EndRow int
    set @StartRow=(@PageIndex-1) * @PageSize +1
    set @EndRow =@StartRow+@PageSize-1
    select * from (
        SELECT ROW_NUMBER() OVER(ORDER BY Id ASC) as rowid ,* FROM MyStudentInfo ) temp
       where temp.rowid between @StartRow AND @EndRow
end
go

exec Proc_P 2,4
go

-- 返回多個(gè)結(jié)果集
create procedure proc_get
@Stuid int,
@Gradeid int
as
begin
   select * from MyStudentInfo where id=@Stuid
   select * from GradeInfo where id=@Gradeid
end
go

-- 執(zhí)行存儲(chǔ)過(guò)程
exec proc_get @Stuid=2,@GradeId=4

--創(chuàng)建名為 GetStuCou_Ext 的返回多個(gè)結(jié)果集的存儲(chǔ)過(guò)程
create procedure GetStuCou_Ext
@StuNo    nvarchar(64),
@Height nvarchar(32)
as
begin
    declare @Var nvarchar(10)    --定義變量

    set @Var='123'        --賦值變量

    --定義表變量
    declare @StuTab table
    (
        ID     int not null primary key,
        StuNo    nvarchar(50) unique,
        Name varchar(50),
        Sex varchar(10),
        Height varchar(10)
    )
    --表變量只能在定義的時(shí)候添加約束

    --定義臨時(shí)表
    create table #Tab
    (
        ID     int not null primary key,
        StuNo    nvarchar(50),
        Name varchar(50),
        Sex varchar(10),
        Height varchar(10)
    )

    alter table #Tab add constraint S_UNIQUE unique(StuNo)

    --臨時(shí)表可以在之后添加約束

    if(@StuNo is not null and @StuNo <> '')
    begin
        insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把數(shù)據(jù)插入表變量
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_StuNo=@StuNo

        insert into #Tab(ID,StuNo,Name,Sex,Height)    --把數(shù)據(jù)插入臨時(shí)表
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_StuNo=@StuNo
    end

    if(@Height is not null and @Height <> '')
    begin
        insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把數(shù)據(jù)插入表變量
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_Height=@Height

        insert into #Tab(ID,StuNo,Name,Sex,Height)    --把數(shù)據(jù)插入臨時(shí)表
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_Height=@Height
    end

    SELECT * FROM @StuTab
    select * from #Tab
end

--執(zhí)行名為 GetStuCou_DSS 的返回多個(gè)結(jié)果集的存儲(chǔ)過(guò)程
execute GetStuCou_Ext '005','185'

create proc GetStus
@StuNo nvarchar(500)
as
begin
    declare @Sql nvarchar(3000)

    if(@StuNo is not null and @StuNo <> '')
    begin
        set @Sql=' select * from Student where S_StuNo in ('+@StuNo+') '
    end

    exec (@Sql)    --執(zhí)行動(dòng)態(tài) sql
end

exec GetStus '003,005,009'        --執(zhí)行存儲(chǔ)過(guò)程 GetStus