SQLServer存储过程入门
1、创建一个返回结果集的存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create procedure firstpro As begin select * from dbo.Person End 执行: execute dbo.firstpro
如下图:
2、创建一个要求输入一个输入参数的存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create procedure twopro @Id varchar(10) --定义一个输入参数 as select * from dbo.Person where Id = @Id --要求Id列与输入的参数相等 执行: execute dbo.twopro ‘1’
如下图:
3、创建一个要求输入两个输入参数的存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create procedure threepro @Id int, --定义一个输入参数 @Name varchar(50) --定义另一个输入参数 as select * from dbo.Person where Id =@Id and Name=@Name 执行: execute dbo.threepro 1,’A’
如下图:
4、创建有返回值的存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE procedure fourpro1 @Id int, @Name varchar(50), @returnage int output as select @returnage = age from dbo.Person where @Id= Id and @Name=Name 执行: declare @returnage int --声明一个变量用来接受执行存储过程的返回值 execute dbo.fourpro1 2,'B',@returnage output select @returnage as age --给返回的列值取一个列名
如下图:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE procedure p6 --定义两个输入参数 @Id int, @Name varchar(50) as declare @returnId int --定义一个int类型的变量 select @returnId = Id from dbo.Person where Id= @Id and Name=@Name --return语句可以接受一个整型表达式(int,smallint,tinyint),而不是一个整型值 return @returnName 执行: declare @id int execute @id= dbo.p6 2,'B' select @id as id
如下图:
5、存储过程输入参数添加默认值
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE procedure fivepro @Id int=2, @Name varchar(50)= 'Bgh' as select * from dbo.Person where Id= @Id and Name=@Name 执行: execute fivepro
备注:dbo.Person