表 A ID SID SName
---- ----- -----
1 0 AAA
2 33 <NULL>
4 34 <NULL>
....表 B
ID Name
----- -----
33 BBB
34 CCC
....列出表A所有数据,
如果SID=0 则取 表A 的 SName
如果SID>0 则取 表B 的 Name存储过程怎么写呢
---- ----- -----
1 0 AAA
2 33 <NULL>
4 34 <NULL>
....表 B
ID Name
----- -----
33 BBB
34 CCC
....列出表A所有数据,
如果SID=0 则取 表A 的 SName
如果SID>0 则取 表B 的 Name存储过程怎么写呢
A.ID,
A.SID,
(Case When SID = 0 Then A.SName When SID > 0 Then B.Name End) As SName
From
A
Left Join
B
On A.SID = B.ID
select a.ID,a.SID,isnull(a.SName,b.Name) from a left join b on a.sid = b.id
from A a left join B b on a.SID=b.SID
SName = (case when SID = 0 then A.SName
when SID > 0 then B.Name
end)
from A left join B
on A.SID = B.ID
Create Table A
(ID Int,
SID Int,
SName Varchar(10))
Insert A Select 1, 0, 'AAA'
Union All Select 2, 33, NULL
Union All Select 4, 34, NULLCreate Table B
(ID Int,
Name Varchar(10))
Insert B Select 33, 'BBB'
Union All Select 34, 'CCC'
GO
--測試
Select
A.ID,
A.SID,
(Case When SID = 0 Then A.SName When SID > 0 Then B.Name End) As SName
From
A
Left Join
B
On A.SID = B.ID
--刪除測試環境
Drop Table A, B
--結果
/*
ID SID SName
1 0 AAA
2 33 BBB
4 34 CCC
*/
Create ProceDure SP_TEST
As
Select
A.ID,
A.SID,
(Case When SID = 0 Then A.SName When SID > 0 Then B.Name End) As SName
From
A
Left Join
B
On A.SID = B.ID
GO
--調用
EXEC SP_TEST但是實際沒有必要使用存儲過程,直接使用SQL語句查詢即可。
--创建存储过程
Create Proc Protest
as
Select A.ID,A.SID,
(Case When SID = 0 Then A.SName When SID > 0 Then B.Name End) As SName
From A Left Join B
On A.SID = B.ID
go
--调用存储过程
Exec Protest