如何写一存储过程实现以下功能：

[valu]

create procedure sp_select_area
@id int
as
begin
--默认选择所有地区
select * from area
end



@id 为空时选择所有地区
@id 不为空时，选择指定的地区
如何实现？

[rucypli]

create procedure sp_select_area
@id int = null
as
begin
if @id is null
select * from area
else
....
end

[lunzi028]

只要有 id 和area 的关系
用if else 即可

[bancxc]

create procedure sp_select_area 
    @id int = null 
as 
begin 
    if @id is null 
      select * from area 
    else 
      select * from area where id=@id
end 

[valu]

create procedure sp_select_area
@id int,
@city nvarchar(20),
@is_zx tinyint
as
begin
--默认选择所有地区
select * from area
end


如果有多个参数呢？

[bancxc]

只能多个
if
elseif
....
else
了

[bancxc]

--可以这样写
create procedure sp_select_area
@id int null,
@city nvarchar(20) null,
@is_zx tinyint null
as
begin

declare @strwhere nvarchar(4000)

set @strwhere = ' 1=1'
if @id is not null
set @strwhere = @strwhere + ' and id='+@id

if @city is not null
set @strwhere = @strwhere + ' and city='''+@city''''
if @city is not null
set @strwhere = @strwhere + ' and is_zx='+@is_zx
exec('select * from area where '+@strwhere)
end

[guguda2008]

自己在程序里先判断一下

[qiqi860819]

create procedure sp_select_area
    @id int
as
begin
    declare @str varchar(1024)
    set @str='select * from area where 1=1';
    if @id is not null
    set @str=@str+' and id='+cast(@id as varchar(4))+'';
    exec (@str)
end

[yuzhifu1]

学习

[navy887]

create procedure sp_select_area 
    @id int, 
    @city nvarchar(20), 
    @is_zx tinyint 
as 
begin 
    if @id is null 
      select * from area 
    else 
      select * from area where area=@city
end 

[老七]

7楼简洁明了

[q85958341]

create procedure sp_select_area
    @id int=null,
    @city nvarchar(20)=null,
    @is_zx tinyint=null
as
begin
    --默认选择所有地区
    select * from area
    where id=isnull(@id,id)
    and city=isnull(@city,city)
    and is_zx=isnull(@is_zx,is_zx)
end
go

[百年树人]

[Quote=引用 4 楼 valu 的回复:]
create procedure sp_select_area
    @id int,
    @city nvarchar(20),
    @is_zx tinyint
as
begin
    --默认选择所有地区
    select * from area
end


如果有多个参数呢？
[/Quote]

create procedure sp_select_area
    @id int=null,
    @city nvarchar(20)=null,
    @is_zx tinyint=null
as
begin
    --默认选择所有地区
    select * from area
    where id=isnull(@id,id)
    and city=isnull(@city,city)
    and is_zx=isnull(@is_zx,is_zx)
end
go

[valu]

请问7楼，在asp.net里怎么实际可能为null的参数传递呢？