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

I don't understand why my code isn't working as I use the identical technique in another SP and it works wonderfully