CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdynamic-sql
Shane
Shane

Reputation: 542

Perform wildcard searches with multiple columns in one parameter using dynamic SQL

My application has only street number & street name fields. I need to declare street name & unit type as one parameter field in my query. I need to perform wildcard searches (on street name field) by any value and return the following results. Possible searches:

Street Number: 1234
Street Name: 
OR
Street Number: 1234
Street Name: Lakeshore SE
OR
Street Number:
Street Name: SE
OR
Street Number: 
Street Name: Lake DR SE

Here is the table results:

+-----------------------------------+-------------------+-----------+
|               STREET_NUMBER       |    STREET_NAME    | UNIT_TYPE |
+-----------------------------------+-------------------+-----------+
|                   1234            |    LAKESHORE DR   |    SE     |
+-----------------------------------+-------------------+-----------+

Here is my stored proc:

@Street varchar (100) = NULL
,@StreetNumber varchar (100) = NULL

AS
BEGIN
SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = N'
SELECT *
FROM ADDRESS_DETAIL
WHERE 1 = 1'

    + CASE WHEN @Street IS NOT NULL THEN
    N' AND STREET_NAME + UNIT_TYPE LIKE ''%'' + @Street + ''%'''  ELSE N'' END
    + CASE WHEN @StreetNumber IS NOT NULL THEN
    N' AND STREET_NUMBER LIKE @StreetNumber' ELSE N'' END

DECLARE @params nvarchar(max) = N'
@Street varchar (60)
,@StreetNumber varchar (60)';

PRINT @sql;

EXEC sys.sp_executesql @sql, @params, 
@Street,
@StreetNumber;

END

Upvotes: 1

Views: 786

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

Major kudos for parameterizing your dynamic sql. That is not something I see most people do. But I don't think you need dynamic sql here. I am a little concerned about your leading wildcards here because it renders the query non-sargable. But that is true with dynamic sql or not.

Here is another way to write this procedure that might be a bit simpler. 

create procedure YourProc
(
    @Street varchar (100) = NULL
    ,@StreetNumber varchar (100) = NULL
) AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM ADDRESS_DETAIL
    WHERE 
    (
        STREET_NAME + UNIT_TYPE LIKE '%' + @Street + '%'
        OR
        @Street IS NULL
    )
    AND
    (
        STREET_NUMBER LIKE '%' + @StreetNumber + '%'
        OR
        @StreetNumber IS NULL
    )
END

My guess is you need to change the first predicate to something like this.

@Street like '%' + STREET_NAME + '%' + UNIT_TYPE + '%'

Another thought...you could replace all your spaces with the wildcard to eliminate multiple spaces causing you issues.

where STREET_NAME + UNIT_TYPE like replace('@STREET', ' ', '%')

Upvotes: 1

Related Questions