问声明标量变量
EN

Stack Overflow用户

提问于 2017-01-11 09:15:02

回答 3查看 3K关注 0票数 0

我正在编写一个错误失败的动态SQL语句。

必须声明标量变量"@@config“

我试图分解代码，并可以确认以下代码确实返回结果：

Declare @@sql as nvarchar(max);
Declare @SourceDatabase as nvarchar(10);
Declare @DestDatabase as nvarchar(10);
Declare @@Harray as nvarchar(max) ;

Set @SourceDatabase = 'D_EXP_CPM';
Set @DestDatabase = 'T_EXP_CPM';

set @@sql = 'SELECT B.Hierarchy_SKey AS ''NEW'', A.Hierarchy_SKey AS ''OLD''
             INTO ##TEMPHCONVERT
             FROM ' + @SourceDatabase + '.[dbo].[DIM_HIERARCHY] A 
             INNER JOIN ' + @DestDatabase + '.[dbo].[DIM_HIERARCHY] B 
                   ON A.Hierarchy_Desc = B.Hierarchy_Desc
             WHERE A.Hierarchy_SKey in (' + @@Harray + ');'

exec (@@sql);

Declare @@config as NVARCHAR(MAX);

SELECT @@config = COALESCE(@@config + ', H' + cast(A.[NEW] as nvarchar), 'H' + cast(A.[NEW] as nvarchar)) 
FROM ##TEMPHCONVERT A;


Print @@config

返回值："H15，H16，H17，H18“

当插入动态SQL语句时，尽管我得到了错误.

Declare @SourceDatabase as nvarchar(10)
Declare @DestDatabase as nvarchar(10)
Declare @@sql as nvarchar(max) --variable to hold sql statements for this stored proc
Declare @@Harray as nvarchar(max) -- variable to hold hierarchy array elements from source

Set @SourceDatabase = 'D_EXP_CPM';
Set @DestDatabase = 'T_EXP_CPM';

Declare @@config as NVARCHAR(MAX);

set @@sql = 
'SELECT 
B.Hierarchy_SKey AS ''NEW'', A.Hierarchy_SKey AS ''OLD''
INTO ##TEMPHCONVERT
FROM ' + @SourceDatabase + '.[dbo].[DIM_HIERARCHY] A 
INNER JOIN ' + @DestDatabase + '.[dbo].[DIM_HIERARCHY] B 
ON A.Hierarchy_Desc = B.Hierarchy_Desc

WHERE A.Hierarchy_SKey in (' + @@Harray + ');'

exec (@@sql);

set @@sql = '
UPDATE A SET A.[Value_Text] = (SELECT @@config = COALESCE(@@config + '', H'' + cast(A.[NEW] as nvarchar), ''H'' + cast(A.[NEW] as nvarchar)) 
FROM ##TEMPHCONVERT A)
FROM ' + @DestDatabase + '.[dbo].[DIM_CONFIG] A ON A.[Config_Desc] = ''JBCODE FTE Map Hierarchy  List'' ;' 

exec (@@sql);

注意，为了使文章尽可能简短，我已经从中间删除了相当多的代码。有人能解释我为什么会出错吗？

我尝试在动态SQL语句中两处声明变量，但都不允许代码运行。我在想，也许这个变量需要以某种方式传递，但我不知道如何将它传递到子查询中。

sql

sql-server

tsql

1元体验云点播

覆盖建站/短视频等热门场景，提供媒资管理、短视频 SDK、小程序插件和播放器云+端一体化能力，助您抓住视频风口

回答 3

Stack Overflow用户

回答已采纳

发布于 2017-01-11 09:28:59

将@@config声明放在@@sql字符串中：

Declare @SourceDatabase as nvarchar(10)
    Declare @DestDatabase as nvarchar(10)
    Declare @@sql as nvarchar(max) --variable to hold sql statements for this stored proc
    Declare @@Harray as nvarchar(max) -- variable to hold hierarchy array elements from source
    Set @SourceDatabase = 'D_EXP_CPM';
    Set @DestDatabase = 'T_EXP_CPM';
set @@sql = 
'SELECT 
B.Hierarchy_SKey AS ''NEW'', A.Hierarchy_SKey AS ''OLD''
INTO ##TEMPHCONVERT
FROM ' + @SourceDatabase + '.[dbo].[DIM_HIERARCHY] A 
INNER JOIN ' + @DestDatabase + '.[dbo].[DIM_HIERARCHY] B 
ON A.Hierarchy_Desc = B.Hierarchy_Desc

WHERE A.Hierarchy_SKey in (' + @@Harray + ');'

exec (@@sql);

set @@sql = 'Declare @@config as NVARCHAR(MAX) 
UPDATE A SET A.[Value_Text] = (SELECT COALESCE(@@config + '', H'' + cast(A.[NEW] as nvarchar), ''H'' + cast(A.[NEW] as nvarchar)) 
FROM ##TEMPHCONVERT A)
FROM ' + @DestDatabase + '.[dbo].[DIM_CONFIG] A ON A.[Config_Desc] = ''JBCODE FTE Map Hierarchy  List'' ;' 
exec (@@sql);

票数 0

Stack Overflow用户

发布于 2017-01-11 11:08:49

我不知道你为什么会有这个错误，但这是对你所拥有的的一次轻微的重写。我喜欢声明一个模板，然后在执行之前替换参数，而不是连接一个长字符串。它只会让我更容易读懂。我将所有相关代码移到@@sql中，并在临时表中返回结果。

Declare @@sql as nvarchar(max);
Declare @SourceDatabase as nvarchar(10);
Declare @DestDatabase as nvarchar(10);
Declare @@Harray as nvarchar(max) ;

Set @SourceDatabase = 'D_EXP_CPM';
Set @DestDatabase = 'T_EXP_CPM';
set @@Harray = '1,2,3';

set @@sql = '
SELECT B.Hierarchy_SKey AS ''NEW'', A.Hierarchy_SKey AS ''OLD''
INTO #TEMPHCONVERT
FROM [[source]].[dbo].[DIM_HIERARCHY] A 
INNER JOIN [[dest]].[dbo].[DIM_HIERARCHY] B 
ON A.Hierarchy_Desc = B.Hierarchy_Desc
WHERE A.Hierarchy_SKey in ([Harray]);

Declare @@config as NVARCHAR(MAX);
SELECT @@config = COALESCE(@@config + '', H'' + cast(A.[NEW] as nvarchar), ''H'' + cast(A.[NEW] as nvarchar)) 
FROM #TEMPHCONVERT A;

select [config] = @@config into ##config
'

set @@sql = replace(@@sql, '[source]', @SourceDatabase)
set @@sql = replace(@@sql, '[dest]', @DestDatabase)
set @@sql = replace(@@sql, '[Harray]', @@Harray)

exec (@@sql);

select * from ##config

drop table ##config

票数 0

Stack Overflow用户

发布于 2017-01-12 01:03:46

声明@config为NVARCHAR(MAX)；

SELECT @config = COALESCE(@config + ', H' + cast(A.[NEW] as nvarchar), 'H' + cast(A.[NEW] as nvarchar)) 
FROM #TEMPHCONVERT A;

set @sql = 'UPDATE A SET A.[Value_Text] = (@config )
FROM ' + @DestDatabase + '.[dbo].[DIM_CONFIG] A WHERE A.[Config_Desc] = ''JBCODE FTE Map Hierarchy  List'' ;'


PRINT 'STEP 15 COMPLETE';