CODEWITHSUNDEEP
sqlsql-servert-sql
THunter
THunter

Reputation: 323

Dynamic sql is giving syntax error.

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'

Upvotes: 1

Views: 1027

Answers (3)

user275683
user275683

Reputation:

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.

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Maybe this can make sense:

...

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

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

Upvotes: 0

cubitouch
cubitouch

Reputation: 1937

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)

Upvotes: 2

Related Questions