select isnull((select top 1 Name from a where id=@id),'默认')
if object_id('tb') is not null drop table tb create table tb ( ID int, [Name] nvarchar(20) ) insert into tb select 1,'张三' union all select 2,'李四'create proc getInfo ( @ID int ) as if exists(select 1 from tb where ID=@ID) begin select [Name] from tb where ID=@ID end else begin select 'huguo' endexec getInfo 1 Name -------------------- 张三(1 行受影响) exec getInfo 3----- huguo(1 行受影响)
说实话,该需求应该通过程序自己来控制,很很很简单的东西 通过sql,就是故意为难sql了,虽然可以实现。假设非得用sql查询实现,用存储过程会更方便些: 如下: create procedure GetNameById @Id int as begin set nocount on select Name from a where Id=@Id if @@rowcount=0 select '默认' as Name end
应该 编写个函数实现 . create function GetName @id int returns @name varchar(50) as if exists(select 1 from tb where ID=@ID) begin select @name=[Name] from tb where ID=@ID end else begin select @name='默认' end
if object_id('tb') is not null drop table tb
create table tb
(
ID int,
[Name] nvarchar(20)
)
insert into tb select 1,'张三'
union all select 2,'李四'create proc getInfo
(
@ID int
)
as
if exists(select 1 from tb where ID=@ID)
begin
select [Name] from tb where ID=@ID
end
else
begin
select 'huguo'
endexec getInfo 1
Name
--------------------
张三(1 行受影响)
exec getInfo 3-----
huguo(1 行受影响)
通过sql,就是故意为难sql了,虽然可以实现。假设非得用sql查询实现,用存储过程会更方便些:
如下:
create procedure GetNameById
@Id int
as
begin
set nocount on select Name
from a
where Id=@Id if @@rowcount=0
select '默认' as Name
end
create function GetName @id int
returns @name varchar(50)
as
if exists(select 1 from tb where ID=@ID)
begin
select @name=[Name] from tb where ID=@ID
end
else
begin
select @name='默认'
end