SQL syntax error using dynamic SQL

I have below code:

    set @SQL='(select top 1 @idOUT = id from prospects where result=0' + @SQL_excludeprospects + ' order by id ASC)'
    print @SQL
    SET @ParmDefinition = N'@idOUT int OUTPUT';
    EXECUTE sp_executesql @SQL, @ParmDefinition, @idOUT=@id OUTPUT;
    SELECT @id;


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.
0
Peter
4/21/2009 9:41:43 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

1 Replies
928 Views

Similar Articles

[PageSpeed] 42

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'

0
Husain
4/21/2009 10:34:06 PM
Reply: