中发表评论之前,请尝试使用
在下面,我需要从不同数据库中获得相同的选择语句的结果。我已经使用光标从master.dbo.sysdatabase选择数据库列表,并将其传递给变量(即@name(,该列表将一个一个接一个地将数据库名称保存。
在"选择语句"下,我传递了与 @name.dbo.currencyconversionrates
一样但是,在运行代码时,我没有得到结果。错误消息: -
msg 102,15级,状态1,第23行不正确的语法近乎'。'。
我的代码:
DECLARE @name nVARCHAR(100)
DECLARE @DBV nVARCHAR(100)
DECLARE @db_cursor CURSOR
SET @db_cursor = CURSOR FAST_FORWARD
FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name IN ('ENT_Benteler','ENT_DEUTSCHE_BANK','ELV_Henry_Schein','ENT_HONDA')
OPEN @db_cursor
FETCH NEXT FROM @db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @DBV= @name
FETCH NEXT FROM @db_cursor INTO @name
--print @DBV
--use @name
select row_id, base_currency,conversion_currency, effective_period_start, effective_period_end, rate, effective_period_start as 'Use this for Script' from @name.dbo.currencyconversionrates
order by CONVERSION_CURRENCY,EFFECTIVE_PERIOD_START
END
CLOSE @db_cursor
DEALLOCATE @db_cursor
请通过纠正我的问题来帮助我。
select row_id, base_currency,conversion_currency, effective_period_start, effective_period_end, rate, effective_period_start as 'Use this for Script'
from @name.dbo.currencyconversionrates
order by CONVERSION_CURRENCY,EFFECTIVE_PERIOD_START
上面的部分不正确,因为不支持from
之后的参数。您应该使用dynamic SQL
,忽略SQL injection
,您可以这样做:
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
select row_id, base_currency,conversion_currency, effective_period_start, effective_period_end, rate, effective_period_start as [Use this for Script]
from ['+ @name +'].dbo.currencyconversionrates
order by CONVERSION_CURRENCY,EFFECTIVE_PERIOD_START'
print (@SQL)
--EXEC (@SQL)
在EXEC
print