如何在联接存储过程运行时动态更改表名

Question

我想制作一个sql存储过程并将其用作水晶报告的数据源。 我按如下方式制作，它按照我想要的那样工作正常。

CREATE PROCEDURE [dbo].[sp_ScanPointPrint]

 @branch varchar(50),
 @tripID int,
 @scanPoint varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
select sp.BranchCode, sp.ScanPoint, sp.TripId, sp.DoneBy,FORMAT(sp.DateTime,'dd MMMM, yyyy hh:mm tt'), sp.Driver, sp.CarNo, sp.ItemShouldBe, sp.ActualTaken, sp.MissedAny, sp.MissedCount, sp.TookExtra, sp.ExtraCount, spT.OrderNo,spt.ItemBarcode, oi.ItemName 
from ScanPointLog as sp
left join TableScanPoint1 as spT on sp.BranchCode = spT.BranchCode and sp.TripId = spT.TripId 
left join OrderItem as Oi on spt.OrderNo=oi.OrderNO and spt.ItemBarcode=oi.ItemBarcode
where sp.BranchCode=@branch and sp.ScanPoint=@scanPoint and spt.IsItem=1 and sp.TripId=@tripID
END
GO

我想执行但无法做的是将TableScanPoint1表名更改为另一个表名。 有 8 个表作为 TableScanPoint1 到 TableScanePont8，结构相同，但数据不同。 这样做的目的是使一个报表设计按照最终用户的意愿使用它。 最终用户将选择表， 在运行时从 vb.net 应用程序分支、三倍 ID 和扫描点。

任何人都可以帮我吗？谢谢

Answer 1

CREATE PROCEDURE [dbo].[sp_ScanPointPrint]

 @branch varchar(50),
 @tripID int,
 @scanPoint varchar(50),
 @tablenametoscan varchar(200)
AS
BEGIN
    SET NOCOUNT ON;
declare @sqltoexecute varchar(max)
set @sqltoexecute = '
select sp.BranchCode, sp.ScanPoint, sp.TripId, sp.DoneBy,FORMAT(sp.DateTime,'dd MMMM, yyyy hh:mm tt'), sp.Driver, sp.CarNo, sp.ItemShouldBe, sp.ActualTaken, sp.MissedAny, sp.MissedCount, sp.TookExtra, sp.ExtraCount, spT.OrderNo,spt.ItemBarcode, oi.ItemName 
from ScanPointLog as sp
left join ' +  @tablenametoscan + ' as spT on sp.BranchCode = spT.BranchCode and sp.TripId = spT.TripId 
left join OrderItem as Oi on spt.OrderNo=oi.OrderNO and spt.ItemBarcode=oi.ItemBarcode
where sp.BranchCode=@branch and sp.ScanPoint=@scanPoint and spt.IsItem=1 and sp.TripId=@tripID '

exec sp_executesql @sqltoexecute 
END
GO

Answer 2

请尝试使用以下查询：

    CREATE PROCEDURE [dbo].[sp_ScanPointPrint]
     @branch VARCHAR(50),
     @tripID INT,
     @scanPoint VARCHAR(50),
     @table VARCHAR(100)
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @query NVARCHAR(max)
    SET @query = '
    select sp.BranchCode, sp.ScanPoint, sp.TripId, sp.DoneBy,FORMAT(sp.DateTime,''dd MMMM, yyyy hh:mm tt''), sp.Driver, sp.CarNo, sp.ItemShouldBe, sp.ActualTaken, sp.MissedAny, sp.MissedCount, sp.TookExtra, sp.ExtraCount, spT.OrderNo,spt.ItemBarcode, oi.ItemName 
    from ScanPointLog as sp
    left join ' +  @table + ' as spT on sp.BranchCode = spT.BranchCode and sp.TripId = spT.TripId 
    left join OrderItem as Oi on spt.OrderNo=oi.OrderNO and spt.ItemBarcode=oi.ItemBarcode
    where sp.BranchCode=@branch and sp.ScanPoint=@scanPoint and spt.IsItem=1 and sp.TripId=@tripID '

    EXEC (@query) 
    END
    GO