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?