I am trying to create a TableAdapter in .NET using a stored procedure, but I'm getting an error "Incorrect syntax near the keyword 'WHERE'".

I have narrowed down the problem in the stored procedure to the following lines:

IF @NumRows > 0
    SET @SQL += N' WHERE T1.RowNumber BETWEEN 1 and 10'
ELSE
    SET @SQL += N' WHERE T1.RowNumber > 0'

@NumRows is an input parameter that defaults to 0.

However, if I remove either of those lines everything works fine (both WHERE clauses work fine by themselves when there is no IF/ELSE statement). It almost looks like .NET is ignoring the IF/ELSE and attempting to add the WHERE clause twice.

It also works fine if I change it like so:

IF @NumRows > 0
    SET @SQL += N''
ELSE
    SET @SQL += N' WHERE T1.RowNumber > 0'

One of the WHERE clauses has been removed, and there's no longer a conflict on the .NET side. So I'm pretty sure it has to do with the 2 WHERE clauses, and not with anything else.

The sproc runs perfectly fine in SQL Server, and it even runs in .NET despite the error if I click "Preview Data". The only problem is that it won't auto-populate the list of fields, which I need for creating a report.

Has anybody seen this before and have a suggestion?