Getting started with SQL Server stored procedures

1. Create a stored procedure that returns a result set

Create procedure Firstpro  as begin    Select *  from dbo. personEnd execution:execute Dbo.firstpro

View Code

Such as:

2. Create a stored procedure that requires input of an input parameter

Create procedure Twopro     @Id varchar (ten-- define an input parameter     as Select *  from where = @Id  -- requires the ID column to be equal to the input parameter Execution: Execute dbo.twopro '1'

View Code

Such as:

3. Create a stored procedure that requires two input parameters

Create procedureThreepro@Id int,--define an input parameter    @Name varchar( -)--define another input parameter as     Select *  fromDbo. PersonwhereId=@Id  andName=@NameExecution:ExecuteDbo.threepro1, ' A '

View Code

Such as:

4. Create a stored procedure with a return value

CREATE procedureFourpro1@Id int,    @Name varchar( -),    @returnage intOutput as    Select @returnage =Age fromDbo. Personwhere @Id=Id and @Name=Name execution:Declare @returnage int  --declares a variable to accept the return value of the executing stored procedure    ExecuteDbo.fourpro12,'B',@returnageOutputSelect @returnage  asAge--take a column name for the returned column value    

View Code

Such as:

CREATE procedureP6--define two input parameters    @Id int,    @Name varchar( -) asDeclare @returnId int --define a variable of type int    Select @returnId =Id fromDbo. PersonwhereId= @Id  andName=@Name--The return statement can accept an integer expression (int,smallint,tinyint) instead of an integer value    return @returnNameExecution:Declare @id intExecute @id=Dbo.p62,'B'Select @id  asId

View Code

Such as:

5. Add default values for stored procedure input parameters

CREATE procedureFivepro@Id int=2,    @Name varchar( -)= 'Bgh' as    Select *  fromDbo. PersonwhereId= @Id  andName=@NameExecution:ExecuteFivepro

View Code

NOTE: dbo. Person

Getting started with SQL Server stored procedures