I have below code:
set @SQL='(select top 1 @idOUT = id from prospects where result=0' + @SQL_excludeprospects + ' order by id ASC)'
SET @ParmDefinition = N'@idOUT int OUTPUT';
EXECUTE sp_executesql @SQL, @ParmDefinition, @idOUT=@id OUTPUT;
I get this output:
(select top 1 @idOUT = id from prospects where result=0 AND id<>2 AND id<>6 AND id<>9 order by id ASC)
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'order'.
I dont see any incorrect syntax! If I execute the statement held in the @SQL variable manually I also get no error...
please mark answers as 'Answered' and post back solutions when you figure stuff out that isnt in the post already.
Try removing the parenthesis around SELECT statement, that should fix it.
set @SQL='select top 1 @idOUT = id from prospects where result=0' + @SQL_excludeprospects + ' order by id ASC'