Stored Procedure and IF Condition

Hey I have a sp and I need a little help. I have a sp that gets a couple of variables and I would like to perform a select and have the like clause for matching partially. Here is what I have.

----------------------
ALTER PROCEDURE [EstatesTrustClientManagement].[et_sp_ETClient_Search]
@Lastname varchar(50)=NULL,
@Firstname varchar(50)=NULL,
@Nickname varchar(50)=NULL,
@Company varchar(100)=NULL,
@Partner integer=NULL

AS

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)



SELECT @sql ='select EstatesTrustClients.*, '

SELECT @sql = @sql + 'lastName + '', '' + firstname AS fullname '

SELECT @sql = @sql + 'from estatestrustclients WHERE 1=1'





IF (@LastName IS NOT NULL) AND (@LastName <> '')
SELECT @sql = @sql + ' AND Lastname LIKE @Lastname'
IF (@Firstname IS NOT NULL) AND (@Firstname <> '')
SELECT @sql = @sql + ' AND Firstname LIKE @Firstname'
IF (@Nickname IS NOT NULL) AND (@Nickname <> '')
SELECT @sql = @sql + ' AND Nickname LIKE @Nickname'
IF (@Company IS NOT NULL) AND (@Company <> '')
SELECT @sql = @sql + ' AND Company LIKE @Company'
IF (@Partner IS NOT NULL) AND (@Partner <> 0)
SELECT @sql = @sql + ' AND PartnerInCharge = @Partner'



SELECT @paramlist = '@Lastname varchar(50),
@Firstname varchar(50),
@Nickname varchar(50),
@Company varchar(100),
@Partner integer'

EXEC sp_executesql @sql, @paramlist,
@Lastname, @Firstname, @Nickname, @Company, @Partner

-----------------------

Except the likes dont work. They look for full lastname, firstname etc. I like like to be able to enter. Jo and have Johnson come up as a record.

Thanks.

Ranger,

"LIKE" requires the wildcard character % to function the way you want it to.

For example,
SELECT *
FROM tblPublisher
WHERE City LIKE 'New %'

... would select all rows where the city starts with "New ".

The SQL in your example would omit the %.

I think you want something like the following (although I am using SQL Server 2005, so I can't test the 2000 code).

declare @LastName varchar(63);
declare @sql varchar(255);

set @adName = 'Jo';

-- and your Lastname line could be
SET @sql = @sql + ' AND Lastname LIKE ' + char(39) + @Lastname + '%' + char(39)

SET works better than SELECT when assigning values to variables.