.NET Error with Dynamic T-SQL
- by Phillip Theriault
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?
