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

>Hi,
>
>I have a stored procedure defined like this:
>
>

CREATE PROCEDURE [dbo].[CustomersSelectLikeLastNameAndNumber] 
>	@cus_LastName varchar(50),
>	@cus_Number nchar(5),
>	@debug bit = 0
>AS
>
>DECLARE @sql        nvarchar(MAX),
>        @paramlist  nvarchar(4000)  
>        
>	SET NOCOUNT ON;
>SELECT @sql =
>	'SELECT [cus_PK]
>      ,[cus_lastname]
>  FROM [dbo].[Customers]
>	WHERE 1 = 1'
>	
>	IF @cus_Number is not null 
>		SELECT @sql = @sql + ' AND [cus_number] LIKE + ''%'' + @cus_Number + ''%'''
>
>	IF @cus_LastName is not null 
>		SELECT @sql = @sql + ' AND [cus_LastName] LIKE @cus_LastName + ''%'' fred'
>
>IF @debug = 1
>   PRINT @sql
>
>SELECT @paramlist = '@cus_Number   varchar(50),
>                     @cus_LastName  nchar(5)'
>
>EXEC sp_executesql @sql, @paramlist,
>                   @cus_Number, @cus_LastName
>
>
>GO
>

>
>When I execute (right click and choose execute) it like this:
>
>

DECLARE	@return_value int
>
>EXEC	@return_value = [dbo].[CustomersSelectLikeLastNameAndNumber]
>		@cus_LastName = N'c',
>		@cus_Number = NULL,
>		@debug = 1
>
>SELECT	'Return Value' = @return_value
>
>GO

>
>I get no records returned.
>
>If I run this code:
>
>

declare @cus_lastname nvarchar(50)
>
>select @cus_lastname = 'c'
>
>SELECT [cus_PK]
>      ,[cus_lastname]
>  FROM [dbo].[Customers]
>	WHERE 1 = 1 AND [cus_LastName] LIKE @cus_LastName + '%'

>
>I get two records returned (as expected).
>
>Can anyone see what I've done wrong in my SP?