>>
I know there is a way to display what the final value of @sSQL, but can't remember it. Can someone tell me how, please? Also, if you can spot what my syntax error is that would be great too! >>
>>
>>Frank, I can't immediately spot the syntax error - however, if you also place a SELECT @sSql at the end of the proc, the procedure will return the string...so you could use that in testing to bring back the value of @sSQL, and perhaps help in your debugging.
>
>Thanks Kevin,
>
>as I posted it I remembered the PRINT command so I added PRINT @sSQL and this came out:
>
>
SELECT [CommissionPK],
> [CommissionsPaid].[Amount],
> [ChequeNumber],
> [ChequeDate],
> [ReceiptNumber],
> [ReceiptDate],
> [ReceiptPK],
> [AgentNumber],
> [FirstName],
> [MiddleName],
> [LastName],
> [AgentPK]
> FROM CommissionsPaid
> inner join ReceiptDetails on ReceiptDetailFK = ReceiptDetailPK
> inner join Receipts on ReceiptFK = ReceiptPK
> inner join Agents on AgentFK = AgentPK
> LEFT OUTER JOIN Cheques ON [ChequeFK] = [ChequePK]
>WHERE 1 = 1
> AND ([LastName] LIKE @AgentName
> AND [ChequeNumber] IS NOT NULL
>Msg 102, Level 15, State 1, Line 24
>Incorrect syntax near 'NULL'.
>
>Can you see anything wrong with that?
Got it! Missing bracket after @AgentName!