Dynamic sql is giving syntax error.

Question

I am executing the following sql. I get a syntax error which is (Incorrect syntax near '=') The query executes fine and gives proper results when executed normally. couldn't understand. plz take a look.

DECLARE @pvchMachineId VARCHAR(100)  = ''

DECLARE @pvchMake VARCHAR(100) = ''

DECLARE @sql NVARCHAR(1000)

SELECT @sql = ' SELECT TOP 20 x.intId, x.vchMachineId, x.AUDenom, x.intGroupId,

x.vchMake, x.vchModel, x.mCurrency

from dbo.Machine x

inner join
(select max(m1.AUDenom) as audenom, m1.vchMachineId

from dbo.Machine m1
left JOIN dbo.ImportedFile ife on m1.intImportedFileId = ife.intId
WHERE ife.dtFileDate >= ''1-1-2013'' AND ife.dtFileDate <= ''1-29-2014'' AND

--following two lines cause the error

(' + @pvchMake + '= ''0'' OR m1.vchMake = @pvchMake) AND

(' + @pvchMachineId +'= ''0'' OR m1.vchMachineId = @pvchMachineId)

group by vchMachineId) y

on x.AUDenom = y.audenom and x.vchMachineId = y.vchMachineId 
ORDER BY x.AUDenom DESC'

Can you show non-dynamic version of your query. It an help to understand what you mean. — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Jan 29, 2014 at 20:14
the best way to check dynamic sql is to use SELECT @sql at last line — Miller
– Miller, Commented Jan 29, 2014 at 20:14
A comment with -- in sql ends when the line ends. Same in dynamic sql. — user2989408
– user2989408, Commented Jan 29, 2014 at 20:19

score 2 · Accepted Answer · 2014-01-29 20:27:55Z

2

Update your query to the following

(@pvchMake = ''0'' OR m1.vchMake = @pvchMake) AND
(@pvchMachineId = ''0'' OR m1.vchMachineId = @pvchMachineId)

than later when you go to execute just pass it in as parameters to sp_executesql function.

EXEC sp_executesql @sql
         ,N'@pvchMachineId VARCHAR(100), @pvchMake VARCHAR(100)'
         ,@pvchMachineId,@pvchMake

or this which is cleaner

Declare @ParametersDefinition NVARCHAR(max) = N'@pvchMachineId VARCHAR(100), @pvchMake VARCHAR(100)'
EXEC sp_executesql @sql, @ParametersDefinition, @pvchMachineId,@pvchMake

In the end you do not want to concatenate your dynamic SQL statement, it opens it up for SQL Injections. Even though it is a valid option it should be avoided at all cost.

edited Jan 29, 2014 at 20:27

answered Jan 29, 2014 at 20:21

user275683

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

1 Comment

THunter Over a year ago

i've executed it from application as well. it works fine

cubitouch · Accepted Answer · 2014-01-29 20:16:43Z

2

This statement :

'(' + @pvchMake + '= ''0'' OR m1.vchMake = @pvchMake)'

Will output, since the variables are not initialized by anything else than '' :

(= '0' OR m1.vchMake = @pvchMake)

Which is not correct syntaxically.

You should use :

'(''' + @pvchMake + '''= ''0'' OR m1.vchMake = @pvchMake)'

Which would output :

(''= '0' OR m1.vchMake = @pvchMake)

edited Jan 29, 2014 at 20:16

answered Jan 29, 2014 at 20:11

cubitouch

1,94715 silver badges28 bronze badges

1 Comment

THunter Over a year ago

i send a 0 or '' from application if i want the OR operator to short circuit. in case a match is required, i send a string value.

Hamlet Hakobyan · Accepted Answer · 2014-01-29 20:18:34Z

0

Maybe this can make sense:

...

(''' + @pvchMake + '''= ''0'' OR m1.vchMake = ''' + @pvchMake +''') AND

(''' + @pvchMachineId +'''= ''0'' OR m1.vchMachineId = ''' + @pvchMachineId + ''')
...

answered Jan 29, 2014 at 20:18

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

3 Comments

user2989408 Over a year ago

+1 @THunter or you could send '''' instead of ''.

Hamlet Hakobyan Over a year ago

@THunter, You can use @SaUce solution if you want to pass @pvchMake and @pvchMake as arguments.

THunter Over a year ago

@HamletHakobyan. i was making a syntax error and its working just fine with the modification

Collectives™ on Stack Overflow

Dynamic sql is giving syntax error.

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related