>>>Hi I have this Stored Procedure which is used to search for receipts:
>>>
>>>

USE [SIAS]
>>>GO
>>>/****** Object:  StoredProcedure [dbo].[ReceiptsSelectByDynamic]    Script Date: 05/29/2012 16:15:50 ******/
>>>SET ANSI_NULLS OFF
>>>GO
>>>SET QUOTED_IDENTIFIER ON
>>>GO
>>>-- =============================================
>>>-- Author:		<Author,,Name>
>>>-- Create date: <Create Date,,>
>>>-- Description:	<Description,,>
>>>-- =============================================
>>>ALTER PROCEDURE [dbo].[ReceiptsSelectByDynamic] 
>>>	@ReceiptNumber varchar(10),
>>>	@ClientName varchar(50),
>>>	@PolicyNumber 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 [dbo].[Receipts].[PolicyHeaderFK],
>>>		[ReceiptAmount],
>>>		[ReceiptDate],
>>>		[ReceiptNumber],
>>>		[ReceiptPK],
>>>	    [ClientFK],
>>>		[PlanFK],
>>>		[PolicyNumber],
>>>		[PolicyPK],
>>>		[Code],
>>>		[Description],
>>>		[ClientNumber],
>>>		[CompanyName],
>>>		[FirstName],
>>>		[MiddleName],
>>>		[LastName]
>>>	FROM [dbo].[Receipts]
>>>		INNER JOIN [dbo].[Policyheaders] ON [dbo].[Receipts].[PolicyHeaderFK] = [PolicyHeaderPK]
>>>		INNER JOIN [dbo].[Policies]	ON [PolicyHeaderPK] = [dbo].[Policies].[PolicyHeaderFK]
>>>		INNER JOIN (SELECT [PolicyHeaderFK], MAX(EffectiveDate) as EffectiveDate 
>>>						FROM [dbo].[Policies]
>>>						GROUP BY [PolicyHeaderFK]) subquery ON [Policies].[PolicyHeaderFk] = subquery.policyHeaderFK AND Policies.EffectiveDate = subquery.EffectiveDate
>>>		inner join [dbo].[Plans] on [PlanFK] = [PlanPK]
>>>		inner join [dbo].[Clients] on [ClientFK] = [ClientPK] ' + @NewLine
>>>
>>>
>>>	IF @PolicyNumber != ''
>>>	BEGIN
>>>        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ([PolicyNumber] LIKE @PolicyNumber)'
>>>	END
>>>   
>>>    IF @ClientName != ''
>>>        SET @sWhereClause = @sWhereClause + @NewLine + '   AND ([LastName] LIKE @ClientName OR [CompanyName] LIKE @ClientName)'
>>>
>>>    IF @ReceiptNumber != ''
>>>        SET @sWhereClause = @sWhereClause + @NewLine + '   AND [ReceiptNumber] LIKE @ReceiptNumber'
>>>
>>>    SET @ParmDefinition = '@PolicyNumber            nvarchar(20),' + @NewLine
>>>                        + '       @ClientName   nvarchar(50),' + @NewLine
>>>                        + '       @ReceiptNumber   nvarchar(20)'
>>>
>>>    SET @sSql = @sSql + @sJoinClause + @sWhereClause
>>>
>>>    EXEC sp_executesql @sSql, @ParmDefinition,
>>>                       @PolicyNumber=@PolicyNumber,
>>>                       @ClientName=@ClientName,
>>>                       @ReceiptNumber=@ReceiptNumber
>>>
>>>END
>>>

>>>
>>>If I pass in a full PolicyNumber like 'WLIF20120047' I get no matches.
>>>
>>>If I pass in 'WLIF2012004%' I get matches with the policynumber 'WLIF20120047'
>>>
>>>Likewise if I pass in 'WLIF20120047%'.
>>>
>>>What is wrong with my code?
>>
>>Have you tried to print your code (print @sSQL) and then test it standalone?
>
>No, how do I do that?
>
>I just added "print @sSQL" after the SET @sSql = @sSql + @sJoinClause + @sWhereClause line, but I didn't get any printout anywhere that I could see

Did you run the alter procedure command? If you did, it should be in the Messages pane, unless it's NULL. Change

print @sSQL
to

print coalesce(@sSQL, 'sSQL IS NULL')