sql - 使用变量调用[数据库].[架构]

Question

我试图操纵这个现有的代码，因为在测试时，它没有引用表的确切位置。我的意思是，它不引用表的数据库名称和架构及其表名称。示例:如果表名为 'package' ，那么目前它只是在更新语句“package”中输出，而不是 'Holidays.dbo.package' 。 Holidays显然是数据库名称。

但问题是，我所指的表可能不在 Holidays 中。数据库，它可能来自其他数据库或模式。所以我不能简单地添加 update 'Holidays.dbo.' + @tablename ，我需要它比这更有活力。

我的问题是，如果我设置两个变量来调用数据库和模式，如何设置变量来引用它们？

下面是我提取的部分代码，我认为与此问题相关:

declare @tablename varchar(MAX)
declare @loop int = 1           

select  a.* into #tmp
from 
(
select  RID,
v.value('local-name(.)', 'VARCHAR(MAX)')  'Field', 
v.value('./text()[1]', 'VARCHAR(MAX)')   'Value'        
from  #XMLTemp
cross apply Field.nodes ('/Record/*') x(v)
where v.value('local-name(.)', 'VARCHAR(MAX)')  not in ('Update', 'Filter', 'Insert', 'Delete')
) as a
where RID = @loop

...

select @tablename = ''
select @tablename = Value
from #tmp
where Field='tableName'
and RID = @loop

...

print 'update ' + @tablename + '

...

select @tablename = Value from #tmp where Field = 'TableName'

...

set @loop = @loop+1

更新:

下面是“ProductPerson”表的 xml，其中包含输入的新值及其先前的值。

<Task xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Record>
    <order>1</order>
    <TableName>ProductPerson</TableName>
    <KeyField>ProductPersonID</KeyField>
    <TableRef>32420</TableRef>
    <Update>
      <FieldName>StatusID</FieldName>
      <OldValue>3</OldValue>
      <NewValue>8</NewValue>
    </Update>
  </Record>
</Task>

下面是 xml 的生成方式:

BEGIN
-- Get details of any changes made
-- First are any update fragments stored
-- (basically these are the data changes)
select 
z.value('(./FieldName/text())[1]', 'VARCHAR(MAX)')   'FieldName',
z.value('(./OldValue/text())[1]', 'VARCHAR(MAX)')    'OldValue',
z.value('(./NewValue/text())[1]', 'VARCHAR(MAX)')    'NewValue',
'Update' [Type]
into #Changes
from #XMLTemp t
cross apply field.nodes('/Record/*') y(z)
where z.value('local-name(.)', 'VARCHAR(MAX)') = 'Update'
and RID = @loop
UNION ALL
-- Now get any Filter changes (from addnlfragment)
-- These aren't data changes but are used for filtering.
select 
z.value('(./FieldName/text())[1]', 'VARCHAR(MAX)')   'FieldName',
'' [OldValue],
z.value('(./FilterValue/text())[1]', 'VARCHAR(MAX)')     'NewValue',
'Filter' [Type]
from #XMLTemp t
cross apply field.nodes('/Record/*') y(z)
where z.value('local-name(.)', 'VARCHAR(MAX)') = 'Filter'
and RID = @loop

-- Only consider "update" types here - as filters may have blank old values for a specific field
set @update = ''
SELECT   @update = COALESCE(@update , '') + 
FieldName + iif (isnull(OldValue, 'NUL') = 'NUL', ' = ' +isnull(OldValue, 'NULL') + ',' ,  ' = ''' +isnull(OldValue, ' NULL ') +''',')
FROM    #Changes
where Type ='Update'                        

-- Remove any extra commas from the end of the generated string
if(RIGHT(@update, 1) = ',')
BEGIN
set @update = substring(@update, 1, len(@update)-1)
END

Answer 1

类似这样的吗？

declare @x  xml=
'<Task xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Record>
    <order>1</order>
    <TableName>ProductPerson</TableName>
    <KeyField>ProductPersonID</KeyField>
    <TableRef>32420</TableRef>
    <Update>
      <FieldName>StatusID</FieldName>
      <OldValue>3</OldValue>
      <NewValue>8</NewValue>
    </Update>
  </Record>
</Task>';


DECLARE @DataBaseName VARCHAR(100)='MyDataBase';
DECLARE @DataBaseSchema VARCHAR(100)='MySchema';

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT One.Record.value('order[1]','int')
      ,'UPDATE ' + @DataBaseName + '.' + @DataBaseSchema + '.' + One.Record.value('TableName[1]','varchar(max)') 
        + ' SET ' + One.Record.value('(Update/FieldName)[1]','varchar(max)') + '=''' + One.Record.value('(Update/NewValue)[1]','varchar(max)') + ''' '
        + ' WHERE ' + One.Record.value('KeyField[1]','varchar(max)') + '=''' +  One.Record.value('TableRef[1]','varchar(max)') + ''';'
FROM @x.nodes('/Task/Record') AS One(Record) 

这是结果:

1   UPDATE MyDataBase.MySchema.ProductPerson SET StatusID='8'  WHERE ProductPersonID='32420';