Get error in string query

Question

I'm a beginner to SQL Server

I wrote this query:

DECLARE @sql nvarchar(1000) = 'UPDATE Work
                               SET [Name] = Programmer, [ImageAddress] = pic.jpg 
                               WHERE Id = 2'

SELECT @sql
EXEC Sp_executesql @sql

but I get this error

Invalid column name 'Programmer'.

Why do I get this error?

Thank you for your help

Gordon Linoff · Accepted Answer · 2014-11-29 13:52:56Z

1

You are dealing with SQL in strings. Quoting the strings becomes a challenge. You need for Programmer to be in single quotes when the query is executed. To get this, you need double single quotes in the string:

DECLARE @sql nvarchar(1000)='
UPDATE Work
    SET [Name] = ''Programmer'', [ImageAddress] = ''pic.jpg'' WHERE Id=2'

select @sql

EXEC Sp_executesql @sql;

Because you are wise enough to use sp_executesql, you should learn about parameters. You can write the query as:

DECLARE @sql nvarchar(1000)='
UPDATE Work
    SET [Name] = @Programmer, [ImageAddress] = @imageaddress WHERE Id=2'

select @sql

EXEC Sp_executesql @sql, N'@programmer nvarchar(255), @imageaddress nvarchar(255)',
     @programmer = N'Programmer', @imageaddress = N'pic.jpg';

This has several advantages besides the quoting. It is safer in terms of SQL injection and it allows SQL Server to cache the execution plans if the query is called more than once.

answered Nov 29, 2014 at 13:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sqlDev Over a year ago

thank you for your answer, but one other question, if i want pass variable instead of string into column value, how i can do it? like this SET [Name] ='+@Name+',

Gordon Linoff Over a year ago

@sqlDev . . . Yes. And in that case I would strongly encourage you to use parameter substitution instead of constructing the values in the string.

Dgan · Accepted Answer · 2014-11-29 13:55:48Z

0

try this:

You need to use '' (Double Quotes for string) Inside Dynamic SQL

DECLARE @sql nvarchar(1000)='

    UPDATE Work

        SET [Name] = ''Programmer'',[ImageAddress] =''pic.jpg'' WHERE Id=2'

    select @sql

EXEC Sp_executesql @sql

edited Nov 29, 2014 at 13:55

answered Nov 29, 2014 at 13:50

Dgan

10.3k1 gold badge33 silver badges51 bronze badges

Collectives™ on Stack Overflow

Get error in string query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related