>>The condittion:
>>

>>IF @cus_LastName is not null 
>>   SELECT @sql = @sql + ' AND [cus_LastName] LIKE @cus_LastName + ''%'' fred' --- What doing FRED here?
>>-- BTW there is no starting '%' also
>>

>>

You declared

'cus_LastName  nchar(5)'

That ways you ALWAYS will have 5 chars. Declare it as nvarchar() instead.



>
>Sorry, I put fred in to cause an error so I could see what the sql returned :)
>
>I don't need the starting '%' unless I am doing a "contains" search. In this case I was doing a "starts with" search, but have adjusted it to a contains search in the below code.
>
>I found an old SP that I used before and adjusted this one to match coming up with this (which works):
>
>

USE [IBC]
>GO
>/****** Object:  StoredProcedure [dbo].[CustomersSelectLikeLastNameAndNumber]    Script Date: 11/26/2012 08:47:43 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>
>-- =============================================
>-- Author:		<Author,,Name>
>-- Create date: <Create Date,,>
>-- Description:	<Description,,>
>-- =============================================
>ALTER PROCEDURE [dbo].[CustomersSelectLikeLastNameAndNumber] 
>	@cus_LastName varchar(50),
>	@cus_Number nchar(5),
>	@debug bit = 0
>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
>
>        
>SELECT @sSql =
>	'SELECT [cus_PK]
>      ,[cus_lastname]
>  FROM [dbo].[Customers]'
>	
>	IF @cus_LastName is not null and @cus_LastName != ''
>	BEGIN
>        SET @sWhereClause = @sWhereClause + @NewLine + ' AND [cus_LastName] LIKE + ''%'' + @cus_LastName + ''%'''
>	END
>	
>	IF @cus_Number is not null and @cus_Number != ''
>	BEGIN
>        SET @sWhereClause = @sWhereClause + @NewLine + ' AND [cus_number] LIKE + ''%'' + @cus_Number + ''%'''
>	END
>    SET @ParmDefinition = '@cus_Number            nchar(5),' + @NewLine
>                        + '       @cus_LastName   nvarchar(50)'
>
>    SET @sSql = @sSql + @sJoinClause + @sWhereClause
>
>IF @debug = 1
>   PRINT @sSql
>   
>    EXEC sp_executesql @sSql, @ParmDefinition,
>                       @cus_Number=@cus_Number,
>                       @cus_LastName=@cus_LastName
>
>END

>
>I am guessing that the main difference was the way I passed in the parameter list to sp_executesql.