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

>

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.