SQL Server Stored Procedure Basics Summary

1. Stored Procedure creation

1 CREATE PROCEDUREsys.sp_student2     @id int,3     @name varchar( -),4     @age int5  as6 BEGIN7     SELECT *  fromStudentWHEREId=@id8 END9 GO

View Code

2. Parameter Definition

@id int,

@name varchar (20)

@age int output--output is represented as an output parameter

DECLARE @where varchar (--declare) Global variables

3, the first method of multi-criteria query:

SET @where='SELECT * from Student WHERE 1=1'    if(@id  is  not NULL)        SET @where=@where+'and id='+" '"+@id+" '"    if(@name  is  not NULL  and @name<>"')        SET @where=@where+'and name='+" '"+@name+" '"    if(@age  is  not NULL)        SET @where=@where+'and age='+@age    if(@sex  is  not NULL  and @sex <>"')        SET @where=@where+'and sex='+" '"+@sex+" '"    EXEC(@where)

View Code

The second method:

Select *  fromStudentwhere(Id= @id or @id  is NULL) and(Name= @name or @name  is NULL) and( Age=@age or @age  is NULL)

View CodeThe third method:

SELECT *  from where Id = ISNULL (@idand=ISNULL(@nameand age       =ISNULL(@age, age)

View Code

SQL Server Stored Procedure Basics Summary