# Miscellaneous > Structured Query Language (SQL) >  Stored Procedure and IF Condition

## ny3ranger

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.

----------


## RalphComputes

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.

----------

