SQL Server Store a reference to a table in a variable

ohfgkhjo 于 2023-05-21 发布在其他

关注(0)|答案(3)|浏览(131)

The goal I am trying to accomplish is to query from a table that is determined dynamically. Below is an example syntax of what I am trying to accomplish.

if exists (select * from tbl_first_option)
    set @tbl = tbl_first_option
else
    set @tbl = tbl_second_option

-- complex statement that selects from table
select * from @tbl

So instead of duplicating the complex statement I can use @tbl . If there are better/easier ways of doing this please let me know.

sql-server

来源：https://stackoverflow.com/questions/76278019/store-a-reference-to-a-table-in-a-variable

3条答案

按热度按时间

dauxcl2d1#

You can actually do this without dynamic SQL, assuming the two tables have the same columns (or you can add/remove columns to match)

SELECT TOP (1) WITH TIES
  t.*
FROM (
    SELECT *, 1 AS TableNumber
    FROM tbl_first_option
    UNION ALL
    SELECT *, 2
    FROM tbl_second_option
) t
ORDER BY TableNumber;

赞(0）回复(0）举报 2023-05-21

plicqrtu2#

You need a dynamic SQL query like

declare @SQL nvarchar(1000)
declare @tbl varchar(50)

if exists (select * from tbl_first_option)
    set @tbl = 'tbl_first_option'
else
    set @tbl = 'tbl_second_option'
 
SET @SQL = 'SELECT * FROM ' + QUOTENAME(@tbl)
 
EXECUTE sp_executesql @SQL

赞(0）回复(0）举报 2023-05-21

tf7tbtn23#

Yes there is the easiest way that you can use and its CASE statement which will be as

SELECT * FROM (CASE 
                     THEN EXISTS (SELECT * FROM tbl_first_option) 
THEN tbl_first_option
        ELSE tbl_second_option
    END
) AS tbl

This query will work on the first table if it's available and from the second table if it's not going to the first table.

赞(0）回复(0）举报 2023-05-21

我来回答

SQL Server Store a reference to a table in a variable

3条答案

相关问题

热门标签

最新问答