>>>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!

John got it right. You're missing closing ) in your AND ([LastName] LIKE @AgentName) -- they are not needed here anyway.

Also, are you passing parameter with % included? If not, you may want to use = instead of like although the result should be the same (and I don't think there will be any difference in the execution plan).