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

I don't know why, but when you use variables SQL Server works this way:

DECLARE @Test TABLE (Fld1 CHAR(50))
INSERT INTO @Test VALUES ('WLIF20120047')
DECLARE @LikePerc nvarchar(50)
DECLARE @LikeWoPerc nvarchar(50)
SET @LikePerc = 'WLIF20120047%'
SET @LikeWoPerc = 'WLIF20120047'
SELECT * FROM @Test WHERE Fld1 LIKE @LikePerc
SELECT * FROM @Test WHERE Fld1 LIKE @LikeWoPerc

But when you use strings it works diferently:

DECLARE @Test TABLE (Fld1 CHAR(50))
INSERT INTO @Test VALUES ('WLIF20120047')
SELECT * FROM @Test WHERE Fld1 LIKE 'WLIF20120047%'
SELECT * FROM @Test WHERE Fld1 LIKE 'WLIF20120047'