SQL command inside a variable varchar - sql server

Asked

Viewed 2,033 times

2

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

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 exec this giving string concatenation error

sql-server

  • Can you explain yourself better? I don’t understand your problem. You don’t know how to update the 3 new columns within the same update, or you are getting an error message (in this case, which)?

  • No. I am unable to put the update command inside a varchar. example: set @Variavel_varchar = 'Update command '

  • still giving error Unclosed quotation mark after the Character string 'Contract Management - Requester Where ID_RESOURCE = 2003'.

  • You can update your question with the full code?

  • 1

    now updated

  • I changed the answer

Show 1 more comment

3 answers

1


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

Don’t forget to add a quotation mark if your field is of the type varchar. Type Ds_en-BR = '''+ @Your value + '''

  • 1

    Thank you. your code was right, the problem was the size of my variable varchar(250)

1

See if you go 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))

  • The variable @DS_PERFIL is being read as a varchar. its contents have not been recognized

1

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

As two Single quotes are the exhaust to include one quotation marks on the string, then to close the string just one quotation mark and to reopen and insert a quotation mark within 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))

  • is giving the error: Unclosed quotation mark after the Character string 'Contract Management - Fin Manager'.

  • And "Fin Manager" is the value of DS_PERFIL, right? ...

  • Already true would be "Financial Manager" this cutting the string

  • You can print STRING_SQL by code and post here?

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.