将表格的引用存储在一个变量中。

huangapple go评论75阅读模式
英文:

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>



huangapple
  • 本文由 发表于 2023年5月18日 13:37:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/76278019.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定