SQL command within a variable varchar - sql server

Question

SQL command within a variable varchar - sql server

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

2

Good afternoon. I need to write a command inside a variable of the varchar format

Set @STRING_SQL = 'update BCS_RESOURCE set DS_DESCRICAO = ''Gestão de Contratos - ''+ @DS_PERFIL + '' ,  [DS_pt-BR] = ''Gestão de Contratos - '+ @DS_PERFIL + ', DS_en-EN = ''Gestão de Contratos - '''+ @DS_PERFIL + ''', DS_es-ES = ''Gestão de Contratos -  '''+ @DS_PERFIL + '''  where ID_RESOURCE = '--+ CAST(@id_Resource as VARCHAR(100))

But I'm still not able to do the exec this by giving string concatenation error

sql-server

asked by anonymous 03.03.2016 / 18:47

3 answers

1

See if you're going now

Set @STRING_SQL = 'update BCS_RESOURCE set DS_DESCRICAO = ''Gestão de Contratos - ''+ @DS_PERFIL ,  [DS_pt-BR] = ''Gestão de Contratos - ''+ @DS_PERFIL + , DS_en-EN = ''Gestão de Contratos - ''+ @DS_PERFIL +, DS_es-ES = ''Gestão de Contratos -  ''+ @DS_PERFIL + where ID_RESOURCE = '--+ CAST(@id_Resource as VARCHAR(100))

03.03.2016 / 19:38

1

You are closing the string with two single quotes when you concatenate.

The two single quotes are the escape to include one quotes in the string, so to close the string, just one quotation mark and to reopen and insert a of the string you use three .

Confused, but I think the code below clarifies and corrects the error.

Set @STRING_SQL = 
    'update BCS_RESOURCE ' +
    'set ' +
        'DS_DESCRICAO = ''Gestão de Contratos - ' + @DS_PERFIL + ''', ' +
        'DS_pt-BR     = ''Gestão de Contratos - ' + @DS_PERFIL + ''', ' +
        'DS_en-EN     = ''Gestão de Contratos - ' + @DS_PERFIL + ''', ' +
        'DS_es-ES     = ''Gestão de Contratos - ' + @DS_PERFIL + ''' ' +
    'where ' +
        'ID_RESOURCE = ' + CAST(@id_Resource as VARCHAR(100))

03.03.2016 / 19:41

Save login input text How to solve margin calculation problem in JavaScript?

score 1 · Accepted Answer

Do.

declare @DS_PERFIL varchar(100) = 'teste', @id_Resource int = 1, @SeuValor varchar(100) =' Seu Valor', @STRING_SQL varchar(max)

Set @STRING_SQL = 'update BCS_RESOURCE set DS_DESCRICAO = ''Gestão de Contratos - '+ @DS_PERFIL + ''' ,  [DS_pt-BR] = ''Gestão de Contratos - '+ @DS_PERFIL + ''', DS_en-EN = ''Gestão de Contratos - '+ @DS_PERFIL + ''', DS_es-ES = ''Gestão de Contratos -  '+ @DS_PERFIL + '''  where ID_RESOURCE =  '+ @DS_PERFIL + ''
print @STRING_SQL



update BCS_RESOURCE 
set DS_DESCRICAO = 'Gestão de Contratos - teste' ,  
[DS_pt-BR] = 'Gestão de Contratos - teste',
 DS_en-EN = 'Gestão de Contratos - teste', 
 DS_es-ES = 'Gestão de Contratos -  teste' 
  where ID_RESOURCE =  teste

Do not forget to add a quotation mark if your field is of type varchar . Type DS_en-BR = '' '+' YourValue + '' '