-
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.
-
ny3ranger
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|