Incorrect Syntax near "=" in Dynamic SQL

Question

Below is the query causing error:

EXECUTE (' UPDATE facetswrk.dbo.ODS_SUBSC_PREM_REPORT ' + ' SET ' + @lcrcolumn_name + ' = ' + @lcrcolumn_total)

what are the values for @lcrcolumn_name and @lcrcolumn_total ? — Marco
– Marco, Commented Feb 5, 2016 at 11:05
if you are updating varchar column then syntex : EXECUTE (' UPDATE facetswrk.dbo.ODS_SUBSC_PREM_REPORT ' + ' SET ' + @lcrcolumn_name + ' = ' ' + @lcrcolumn_total+''') — Mukesh Kalgude
– Mukesh Kalgude, Commented Feb 5, 2016 at 11:09
Side note : use sp_executesql to reduce sql injection chance — Arunprasanth K V
– Arunprasanth K V, Commented Feb 5, 2016 at 11:14
sp_executesql by itself won't protect from SQL Injection if the statement is generated by string concatenation. Using parameterized queries will — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2016 at 14:48

JassyJov · Accepted Answer · 2016-02-05 11:08:22Z

1

Your syntax is ok, probably you have wrong valye for column name, or you need to cast @lcrcolumn_tot as nvarchar.

Give us the value for the variable, pr check by yourself with the flowing statement:

declare @lcrcolumn_name nvarchar(50) = 'blabla',
        @lcrcolumn_tot nvarchar(50) = 10
declare @sql nvarchar(4000);

set @sql = ' UPDATE facetswrk.dbo.ODS_SUBSC_PREM_REPORT SET ' + @lcrcolumn_name + ' = ' + @lcrcolumn_tot

print @sql
execute(@sql)

answered Feb 5, 2016 at 11:08

JassyJov

2041 silver badge9 bronze badges

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

Comments

Gaurav A · Accepted Answer · 2016-02-05 11:08:35Z

0

Best is to print the dynamic sql before you execute it to understand what is causing the error, you may have some data value in @lcrcolumn_name and @lcrcolumn_total which may be creating the problem.

answered Feb 5, 2016 at 11:08

Gaurav A

313 bronze badges

Collectives™ on Stack Overflow

Incorrect Syntax near "=" in Dynamic SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related