Hi,

I have the below Stored Procedure which when it runs gives me the error: Incorrect syntax near 'NULL'

USE [SIAS]
GO
/****** Object:  StoredProcedure [dbo].[CommissionsPaidSelectByDynamic]    Script Date: 01/07/2011 09:13:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CommissionsPaidSelectByDynamic] 
	@AgentNumber varchar(10),
	@AgentName varchar(50),
	@CommissionsPaidStatus varchar(20),
	@StartDate varchar(20),
	@EndDate varchar(20)
AS
	SET NOCOUNT ON;
BEGIN

    DECLARE @sSql nvarchar(4000)
    DECLARE @sWhereClause nvarchar(4000)
    DECLARE @sJoinClause nvarchar(4000)
    DECLARE @ParmDefinition nvarchar(4000)
    DECLARE @NewLine nvarchar(4000)

    SET @NewLine = CHAR(13) + CHAR(10)

    SET @sWhereClause = 'WHERE 1 = 1' -- Initialise

    SET @sJoinClause = '' -- Initialise

    SET @sSql = '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] ' + @NewLine


	IF @AgentNumber != ''
	BEGIN
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ([AgentNumber] LIKE @AgentNumber)'
	END
   
    IF @AgentName != ''
	BEGIN
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ([LastName] LIKE @AgentName'
	END
	
    IF @CommissionsPaidStatus = 'Paid'
    BEGIN
		SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ChequeNumber] IS NOT NULL'
	END
	ELSE
	BEGIN
		SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ChequeNumber] IS NULL'
	END
		
    IF @StartDate != ''
	BEGIN
        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ([ReceiptDate] >= @StartDate'
	END
	
    IF @EndDate != ''
    BEGIN
		SET @sWhereClause = @sWhereClause + @NewLine + '   AND ([ReceiptDate] <= @EndDate'
	END
    SET @ParmDefinition = '@AgentNumber            nvarchar(20),' + @NewLine
                        + '       @AgentName   nvarchar(50),' + @NewLine
                        + '       @CommissionsPaidStatus   nvarchar(20),' + @NewLine
                        + '       @StartDate   nvarchar(20),' + @NewLine
                        + '       @EndDate   nvarchar(20)'

    SET @sSql = @sSql + @sJoinClause + @sWhereClause

    EXEC sp_executesql @sSql, @ParmDefinition,
                       @AgentNumber=@AgentNumber,
                       @AgentName=@AgentName,
                       @CommissionsPaidStatus=@CommissionsPaidStatus,
                       @StartDate=@StartDate,
                       @EndDate=@EndDate

END

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!

Thanks