Results 1 to 2 of 2

Thread: Stored Procedure and IF Condition

  1. #1
    Join Date
    Jul 2008
    Posts
    4

    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.

  2. #2
    Join Date
    Aug 2008
    Location
    Florida
    Posts
    1

    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
  •