英文:
Store a reference to a table in a variable
问题
我正在尝试实现的目标是从一个动态确定的表中查询。以下是我试图实现的示例语法。
if exists (select * from tbl_first_option)
set @tbl = tbl_first_option
else
set @tbl = tbl_second_option
-- 从表中选择的复杂语句
select * from @tbl
因此，我可以使用 @tbl 来避免重复复杂语句。如果有更好/更简单的方法，请告诉我。
英文:
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.
答案1
得分: 1
你需要像这样的动态SQL查询：
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
英文:
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
答案2
得分: 1
以下是翻译好的部分：
你可以在不使用动态SQL的情况下实现这一点，假设这两个表具有相同的列（或者您可以添加/删除列以匹配）：
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;
英文:
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;
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论