我有两个具有相同列名的表，并且我想根据一个参数值从一个或另一个获取数据。我想到的是一些类似的东西
ALTER PROCEDURE [dbo].[traxs_SyncLists_Locations_Search]
@session_id varchar(30),
@get_value int,
@predicate varchar(50),
@combo varchar(30)
AS
SELECT
L.id AS id,
L.location AS value
FROM
CASE
WHEN @combo = 'box' THEN vw_WarehouseLocationBox
WHEN @combo = 'bay' THEN vw_WarehouseLocationBay
END L
LEFT JOIN traxs_temp..__LocationsSyncLists T ON T.session_id = @session_id AND T.id = L.id
WHERE
L.whse_no = @get_value AND
ISNULL(T.archive, 1) = 1 AND
L.location LIKE @predicate + '%'
这不是正确的SQL语法。经过研究，我写了
SELECT
L.id AS id,
L.location AS value
FROM
vw_WarehouseLocationBox L
LEFT JOIN traxs_temp..__LocationsSyncLists T ON T.session_id = @session_id AND T.id = L.id
WHERE
@combo = 'box' AND -- TRICK
L.whse_no = @get_value AND
ISNULL(T.archive, 1) = 1 AND
L.location LIKE @predicate + '%'
UNION
SELECT
L.id AS id,
L.location AS value
FROM
vw_WarehouseLocationBay L
LEFT JOIN traxs_temp..__LocationsSyncLists T ON T.session_id = @session_id AND T.id = L.id
WHERE
@combo = 'bay' AND -- TRICK
L.whse_no = @get_value AND
ISNULL(T.archive, 1) = 1 AND
L.location LIKE @predicate + '%'
但是我总是为重复一些代码感到难过。它只有2个表，但是如果以后有10个表（可能），我应该重复10次吗？还是我已经看到很多人反对使用动态SQL，还是应该使用动态SQL？
该结构看起来像typeof，其中WarehouseLocation是通用类型，而Box，Bay等是扩展类型。
您的查询似乎只需要通用信息。如果您有权这样做，我将在前两个视图vw_WarehouseLocation之上创建一个视图，并在此处使用您已经确定的联合方法维护组合逻辑，但还要包含一个附加字段“ whse_type”以标识typeof。
create view [vw_WarehouseLocation]
as
select
[id] as [id]
, [location] as [location]
, [whse_no] as [whse_no]
, 'bay' as [whse_type]
from [vw_WarehouseLocationBay]
union all
select
[id] as [id]
, [location] as [location]
, [whse_no] as [whse_no]
, 'box' as [whse_type]
from [vw_WarehouseLocationBox]
现在，您不仅可以在当前SP中加入该视图，而且可以在任何其他对象中加入此视图，而无需在此处重复逻辑，即使该视图在视图中确实显得笨拙且重复。此方法更可支持“修补设计”。
例如，
select
L.id
, L.location
from vw_WarehouseLocation L
left join traxs_temp..__LocationsSyncLists T
on T.id = L.id
where L.[whse_type] = @combo
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句