create procedure sp_select_area
@id int
as
begin
--默认选择所有地区
select * from area
end@id 为空时选择所有地区
@id 不为空时,选择指定的地区
如何实现?
@id int
as
begin
--默认选择所有地区
select * from area
end@id 为空时选择所有地区
@id 不为空时,选择指定的地区
如何实现?
解决方案 »
- SQL2000数据库 收缩数据库的问题...只经行修改查询...已经用backup log清空了日志...也用DBCC SHRINKDATABASE ...
- 菜鸟请教:关于数据库备份的问题,请指点一下!!!
- 關于update問題
- 在ACCESS中能否实现HASHSTRING加密啊
- HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!
- 四舍五入
- 求助!~存储过程创建一个表时,列属性不确定如何解决!超急~~~~
- 汇总问题
- 英文软件不能安装在中文XP下
- 请问SQL server2000 的全文检索是真正意义上的全文检索吗
- 请教关于存储过程的设计问题
- sql 试图操作问题
@id int = null
as
begin
if @id is null
select * from area
else
....
end
用if else 即可
@id int = null
as
begin
if @id is null
select * from area
else
select * from area where id=@id
end
@id int,
@city nvarchar(20),
@is_zx tinyint
as
begin
--默认选择所有地区
select * from area
end
如果有多个参数呢?
if
elseif
....
else
了
--可以这样写
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='+@idif @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
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
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
@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
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