Вызов замены sql в динамическом sql

Я пытаюсь обновить строки данных с помощью строки замены (созданной другим SP). У меня есть следующая таблица данных, в которой показаны значения expacct и замены glseg1 и glseg2 по умолчанию:

Account Structure Table

Я пытаюсь обновить номер expacct с помощью обновленного номера expacct, сгенерированного ReplacementString. ReplacementString находится в таблице и связан с expacct по умолчанию:

enter image description here

Каждый раздел expacct (число, установленное между дефисами) может быть обновлено. Вкратце, для каждого раздела expacct ReplacementString должна принимать значение expacct по умолчанию, использовать то, что есть в настоящее время, если перезапись не разрешена (например, подстрока ('@ Acct', 1,4)) или перезаписывать значением из назначенный столбец glseg, используя значение по умолчанию, если значение glseg не существует (например, rtrim (ltrim (isnull (nullif (d.glseg2, ''), substring ('@ Acct', 10,4)))))

У меня есть несколько работающая версия кода, но строка замены не обновляется для каждой обновляемой строки, похоже, что используемая ReplacementString является последней в приведенной выше таблице.

Что мне нужно увидеть в этой выходной таблице, так это expacct для ID 1, равного 6720-010-0000 (значение по умолчанию для первого набора, значение glseg1 для второго набора и 0000 для третьего набора, поскольку glseg2 пуст). Обновленное значение expacct для строки с ID 2 верное.

Мы должны поддерживать SQL до версии 2008 R2, поэтому, пожалуйста, ограничьте решения теми, которые работают с SQL 2008 R2.

enter image description here

Вот полный код SQL, который у меня есть:

if object_id(N'tempdb..#TimeData') is not null drop table #TimeData
create table #TimeData(ID int, expacct varchar(45), glseg1 varchar(10), glseg2 varchar(10))
insert into #TimeData
values(1, '6720-000-0000', '010', '')
    , (2, '6720-999-0000', '030', '0404')
select * from #TimeData

-- create table of all possible account numbers from ALL_TimeCardDetail with their associated replacement strings
if object_id(N'tempdb..#AccountNumbers') is not null drop table #AccountNumbers
create table #AccountNumbers(Original varchar(100), ReplacementString nvarchar(max), Updated varchar(100))

insert into #AccountNumbers
values('6720-000-0000', 'substring(''@Acct'',1,4) + ''-'' + rtrim(ltrim(isnull(nullif(d.glseg1, ''''), substring(''@Acct'',6,3)))) + ''-'' + rtrim(ltrim(isnull(nullif(d.glseg2, ''''), substring(''@Acct'',10,4))))', '')
, ('6720-999-0000', 'substring(''@Acct'',1,4) + ''-'' + substring(''@Acct'',6,3) + ''-'' + rtrim(ltrim(isnull(nullif(d.glseg2, ''''), substring(''@Acct'',10,4))))', '')

select * from #AccountNumbers


declare @cmd nvarchar(max)
select @cmd = N'update #TimeData set expacct = ' + replace(n.ReplacementString, '@Acct', d.expacct) + '
from #TimeData d
    inner join #AccountNumbers n
        on d.expacct = n.Original'
from #TimeData d
    inner join #AccountNumbers n
        on d.expacct = n.Original

print @cmd
exec (@cmd)

select * from #TimeData

Привет. Я не уверен, что вы пытаетесь сделать. Если я хорошо понимаю, 010 нужно заменять ничем, а 030 на 0404? Но вы не использовали 010 или 030 в своем примере! Выложите, пожалуйста, ожидаемый результат.

DanB 13.09.2018 20:21

Ух. Это совершенно новый уровень возможностей SQL-инъекций. Пожалуйста, закрепите этот стол своей жизнью, заменив его нитками, и примените к нему одитинг для хорошей меры. На самом деле, настоятельно рекомендуется отказаться от этого вместо этого - сколько различных заменяющих строк вам нужно В самом деле, и не можете ли вы сократить это до нескольких случаев, которые вы кодируете простым числом и включаете с помощью CASE? Или как насчет использования старых добрых регулярных выражений, обрабатываемых клиентом (и если данные не могут покинуть базу данных, используя сборки CLR для передачи функциональности регулярного выражения в SQL)?

Jeroen Mostert 13.09.2018 20:43

@DanielBlais См. Абзац, который начинается с «Что мне нужно увидеть в этой выходной таблице…» (я разбил его на отдельный абзац, чтобы сделать его более очевидным).

BrianKE 13.09.2018 20:47

Я копирую-вставляю-запускаю ваш код и получаю ожидаемый результат. 1 = 6720-999-0000 и 2 = 6720-999-0404.

DanB 13.09.2018 20:50

@DanielBlais Результат, который мне нужен для ID 1, - 6720-010-0000, а не 6720-999-0000. Есть два разных строковых значения замены, и для всех замен используется только последнее.

BrianKE 13.09.2018 21:04

Проблема в том, что выберите @cmd = N'update ... сохранит последнюю команду обновления в @ cmd. Когда вы запустите этот запрос, он будет основан на последнем результате. Вы должны использовать другую логику или использовать курсор.

DanB 13.09.2018 21:11
0
6
94
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Это работает, но не оптимально с точки зрения производительности:

SET NOCOUNT OFF

if object_id(N'tempdb..#TimeData') is not null drop table #TimeData
create table #TimeData(ID int, expacct varchar(45), glseg1 varchar(10), glseg2 varchar(10))
insert into #TimeData
values(1, '6720-000-0000', '010', '')
    , (2, '6720-999-0000', '030', '0404')
select * from #TimeData

-- create table of all possible account numbers from ALL_TimeCardDetail with their associated replacement strings
if object_id(N'tempdb..#AccountNumbers') is not null drop table #AccountNumbers
create table #AccountNumbers(id int identity, Original varchar(100), ReplacementString nvarchar(max), Updated varchar(100))

insert into #AccountNumbers (original, ReplacementString, updated)
values('6720-000-0000', 'substring(''@Acct'',1,4) + ''-'' + rtrim(ltrim(isnull(nullif(d.glseg1, ''''), substring(''@Acct'',6,3)))) + ''-'' + rtrim(ltrim(isnull(nullif(d.glseg2, ''''), substring(''@Acct'',10,4))))', '')
, ('6720-999-0000', 'substring(''@Acct'',1,4) + ''-'' + substring(''@Acct'',6,3) + ''-'' + rtrim(ltrim(isnull(nullif(d.glseg2, ''''), substring(''@Acct'',10,4))))', '')

select * from #AccountNumbers

declare @cmd nvarchar(max)
declare @id int
declare c cursor for select id from #AccountNumbers
open c
fetch next from c into @id
while @@FETCH_STATUS = 0
begin
    select @cmd = N'update #TimeData set expacct = ' + replace(n.ReplacementString, '@Acct', d.expacct) + '
    from #TimeData d
        inner join #AccountNumbers n
            on d.expacct = n.Original
    where n.id = ' + convert(varchar, @id)
    from #TimeData d
        inner join #AccountNumbers n
            on d.expacct = n.Original
    where n.id = @id
    print @cmd
    exec (@cmd)

    fetch next from c into @id
end
close c
deallocate c

select * from #TimeData

Другие вопросы по теме