>>>>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')

OK, I didn't know where it prints, but I now see a Messages tab which has the SQL in it and it looks fine.