SqlServer存储过程基础小结

1、存储过程创建

1 CREATE PROCEDURE sys.sp_student
2     @id int,
3     @name varchar(20),
4     @age int
5 AS
6 BEGIN
7     SELECT * from Student WHERE Id=@id
8 END
9 GO

View Code

2、参数定义

@id int,

@name varchar(20)

@age int OUTPUT --OUTPUT表示为输出参数

declare @where varchar(200) --declare 全局变量

3、多条件查询

第一种方法：

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

第二种方法：

select * from Student 
where
     (Id= @id or @id is null) and
     (Name = @name or @name is null) and
     (Age=@age or @age is null)

View Code

第三种方法：

SELECT * FROM Student where 
　　Id= ISNULL(@id,Id) AND
　　Name = ISNULL(@name,Name) AND
      Age=ISNULL(@age,Age)

View Code