CODEWITHSUNDEEP
sqlsql-serverstored-procedures
sqlDev
sqlDev

Reputation: 5

Get error in string query

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

Upvotes: 0

Views: 139

Answers (2)

Dgan
Dgan

Reputation: 10295

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 1

Related Questions