sql - SQL Server：从@variable_table_name 中选择（嵌套变量的问题）

Question

我需要选择一个变量表名，这在 SQL Server 中似乎不起作用（参见示例 nr 2）。

我尝试做一个中间步骤，用我的变量表名作为静态文本创建一个字符串（参见示例 nr3），但这也失败了，可能是因为我有嵌套变量。

任何人都可以帮我解决它吗？

------------Example Nr 1--------------
-- Working query without variable Table

DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'SELECT ';
SELECT @Sql = @Sql +'['+[Modell Pset (S1 Slab)]+']'+ ' AS ''' +''+[C3D Pset (S1 Slab)]+''', ' FROM [MTO_Psets_S1_Slab]
PRINT @sql

--Result of "print @sql": SELECT [c3d:abschnitt] AS 'Abschnitt', [ALL] AS 'Eigenschaft_1', [ALL] AS 'Eigenschaft_2', [Geschoss] AS 'Geschoss', [ALL] AS 'Ist_Aussen', [ALL] AS 'Material_1', [ALL] AS 'Material_2', [ALL] AS 'Objekt', [c3d:teilprojekt] AS 'Teilprojekt', 

SET @Sql = (SELECT LEFT(@Sql, LEN(@Sql)-1));
SET @Sql = @Sql + ' FROM [modell_properties_slab]';
EXEC sp_executesql @Sql;

------------Example Nr 2--------------
-- When I replace table with a variable, the query doesn't work anymore

DECLARE @Sql NVARCHAR(MAX);
DECLARE @Table NVARCHAR(MAX);

SET @table = '[MTO_Psets_S1_Slab]'
SET @Sql = 'SELECT ';
SELECT @Sql = @Sql +'['+[Modell Pset (S1 Slab)]+']'+ ' AS ''' +''+[C3D Pset (S1 Slab)]+''', ' FROM @table
SET @Sql = (SELECT LEFT(@Sql, LEN(@Sql)-1));
SET @Sql = @Sql + ' FROM [modell_properties_slab]';
EXEC sp_executesql @Sql

-- Error message:    
-- Msg 1087, Level 16, State 1, Line 24     
-- Must declare the table variable "@Table".

------------Example Nr 3--------------
-- When I rewrite the query to have the table inside the apostrophes,   
-- it doesn't work (compare the result of "print @sql" with the example nr1)

declare @slab nvarchar(MAX) ='slab'
DECLARE @intermediate_step nvarchar(MAX);
set  @intermediate_step = '''[''[Modell Pset (S1 Slab)]+'']''+ '' AS '''''' +''''+[C3D Pset (S1 Slab)]+'''''', '' FROM [MTO_Psets_S1_'+@Slab+']'
print @intermediate_step

--Result of "print @intermediate_step": '['[Modell Pset (S1 Slab)]+']'+ ' AS ''' +''+[C3D Pset (S1 Slab)]+''', ' FROM [MTO_Psets_S1_slab]

DECLARE @Sql nvarchar(MAX);
SET @Sql = 'SELECT ';
SELECT @Sql = @Sql + @intermediate_step
print @sql
--Result of "print @sql": SELECT '['[Modell Pset (S1 Slab)]+']'+ ' AS ''' +''+[C3D Pset (S1 Slab)]+''', ' FROM [MTO_Psets_S1_slab]

SET @Sql = (SELECT LEFT(@Sql, LEN(@Sql)-1));
SET @Sql = @Sql + ' FROM [modell_properties_slab]';
EXEC sp_executesql @Sql;

我将在查询中使用的表和工作查询“example nr 1”中的结果表附加为 JPG：MTO_Psets_S1_Slab、modell_properties_slab、结果查询

MTO_Psets_S1_Slab modell_properties_slab 结果查询

标签： sqlsql-server

Answer 1

我像这样重写了查询，它可以工作：

exec('

declare @t as table ([C3D Pset (S1 '+@slab+')] nvarchar (100), [Modell Pset (S1 '+@slab+')] nvarchar (100));
DECLARE @Sql nvarchar(MAX);

insert @t ([C3D Pset (S1 '+@slab+')] , [Modell Pset (S1 '+@slab+')] )
select * from [MTO_Psets_S1_'+@slab+']

SET @Sql = ''SELECT '';
SELECT @Sql = @Sql +''[''+[Modell Pset (S1 Slab)]+'']''+ '' AS '''''' +''''+[C3D Pset (S1 Slab)]+'''''', '' FROM @t

SET @Sql = (SELECT LEFT(@Sql, LEN(@Sql)-1));
SET @Sql = @Sql + '' FROM [modell_properties_'+@slab+']'';
EXEC sp_executesql @Sql
')