dynamic query not working properly

Question

I am trying to write a dynamic query. The search criteria will come from an ASP page and be passed over to a stored procedure in the SQL Server 2005 Express database. The search is not giving any errors but it returns all data in the database and does not filter based on the variables. someone please help?

I am posting the stored procedure here:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spSearchResource]
     @ResourceID int,
     @Name varchar(75),
     @City varchar(75),
     @State varchar(2), 
     @County varchar(30),
     @Specialty varchar(100),
     @Zip varchar(5),
     @English bit,
     @Spanish bit,
     @French bit,
     @Italian bit,
     @Chinese bit,
     @Japanese bit,
     @GenderType varchar(8),
     @Within int,
     @Children bit,
     @Adolescents bit,
     @Adults bit,
     @Geriatrics bit,
     @Insurance varchar(50)
AS
BEGIN

    DECLARE @strSql varchar(4000);

     SET @strSql = 'select r.resourceid, r.flag, r.note, r.Name, r.ContactName,  r.Website, r.Email, >r.GenderType, loc.Street, loc.city, loc.State, loc.Zip, loc.County,phone.areacode,
                 phone.phonenum,phone.extension,spec.specialty, 
     ins.insurance,pop.children,pop.Adolescents,     
     pop.adults,pop.geriatrics,lan.english,lan.spanish,lan.french,lan.italian,lan.chinese,lan.japanese
     from resource r left outer join resourcelocation loc on (r.resourceid = loc.resourceid)
     left outer join resourcephone phone on (r.resourceid = phone.resourceid)
     left outer join resourceinsurance ins on (r.resourceid = ins.resourceid)
     left outer join resourcepopulation pop on (r.resourceid = pop.resourceid)
     left outer join resourcespecialty spec on (r.resourceid = spec.resourceid)
     left outer join  resourcelanguage lan on (r.resourceid = lan.resourceid) '

        if (@ResourceID is not null) 
            SET @strSql = @strSql + 'and r.resourceid = ' + (CONVERT(VARCHAR(10),@ResourceID))

        if (@Name is not null)
            SET @strSql = @strSql + 'and r.Name like '+''''+  @Name+'%'''

        if (@City is not null)
            SET @strSql = @strSql + 'and loc.city like '+''''+  @City+'%'''

        if (ltrim(rtrim(@State)) is not null)
            SET @strSql = @strSql + 'and loc.State = trim(@State) '

        if (ltrim(rtrim(@Zip)) is not null)
            SET @strSql = @strSql + 'and loc.Zip = trim(@Zip) '

        if (ltrim(rtrim(@County)) is not null)
            SET @strSql = @strSql + 'and loc.County like trim(@County) '

        if (ltrim(rtrim(@specialty)) is not null)
            SET @strSql = @strSql + 'and spec.specialty = trim(@spcialty) '

        if (ltrim(rtrim(@insurance)) is not null)
            SET @strSql = @strSql + 'and ins.insurance = trim(@insurance) '

        if (@English = 1)
            SET @strSql = @strSql + 'and lan.english = @English'

        if (@Spanish = 1)
            SET @strSql = @strSql + 'and lan.spanish = @Spanish '

        if (@French = 1)
            SET @strSql = @strSql + 'and lan.french = @French '

        if (@Italian = 1)
            SET @strSql = @strSql + 'and lan.italian = @Italian '

        if (@Chinese = 1)
            SET @strSql = @strSql + 'and lan.Chinese = @Chinese '

        if (@Japanese = 1)
            SET @strSql = @strSql + 'and lan.japanese = @Japanese '

        if (ltrim(rtrim(@GenderType)) != 0)
            SET @strSql = @strSql + 'and r.GenderType like trim(@GenderType) '

        if (@children = 1)
            SET @strSql = @strSql + 'and pop.children = @children '

        if (@Adolescents = 1)
            SET @strSql = @strSql + 'and pop.Adolescents = @Adolescents '

        if (@adults = 1)
            SET @strSql = @strSql + 'and pop.adults = @adults '

        if (@geriatrics = 1)
            SET @strSql = @strSql + 'and pop.geriatrics = @geriatrics '

        print @strSql;
        execute (@strSql);
    END

So what prints when it prints the string? – JNK Jul 08 '13 at 15:08 — JNK, Jul 08 '13 at 15:08

Michael Fredrickson · Answer 1 · 2013-07-09T16:01:12.933

2

The problem is that your filters aren't being added to a WHERE clause, and instead are being added as conditions to your last LEFT OUTER JOIN. When the criteria isn't met, it is only affecting that last JOIN, and not your other results, which is why you're getting all rows returned.

To fix this, add a WHERE clause following to your initial SQL string:

SET @strSql = 'select r.resourceid, r.flag, r.note, r.Name, r.ContactName, 
...
left outer join resourcelanguage lan on (r.resourceid = lan.resourceid)
where 1 = 1'

Any additional criteria will be added to your initially always true (1=1) WHERE clause, this should properly filter results.

NOTE: Your current implementation is vulnerable to SQL Injection.

EDIT: Since City and State are in the same table, and one works as expected and not the other, I don't think this is related to your LEFT OUTER JOIN not working as expected.

Instead, I'm thinking it has something to do with the trimming that is done against State but not against City.

First, it doesn't do anything to LTRIM, RTRIM the value before checking if it IS NOT NULL. Either it's NULL or it isn't, and trimming won't change that.

Next, in your dynamic SQL, you call a TRIM function. This isn't a built in T-SQL function, so unless you have your own user defined function called TRIM, this should actually be giving you a run-time error.

edited Jul 09 '13 at 16:01

answered Jul 08 '13 at 15:08

Michael Fredrickson

36,839
5
92
109

+1: This is correct except that any conditions that are added to the `WHERE` clause that reference any table other than `r` ([resource]) will effectively turn the corresponding `LEFT OUTER JOIN` into an `INNER JOIN`. – RBarryYoung Jul 08 '13 at 16:10
That might be the new problem I am having, When I search by only city, it still gives me the correct answer. But when I search by only the state. The query returns all the rows in the db even if it is not the correct state. The state comes from a drop down list in the asp form. Do you know how to correct this issue? – JerseyGirl1201 Jul 09 '13 at 04:10
@RBarryYoung Good point, but since the additional conditions are acting as filters, my guess would be that converting the `LEFT OUTER JOIN` to an `INNER JOIN` would be the expected behavior... – Michael Fredrickson Jul 09 '13 at 16:02
@MichaelFredrickson Yes, but in an `OUTER JOIN` the usual expectation is that a condition on the outer table (say `resourceLanguage`) will only filter that table and not also filter out the inner table's base rows (`resource`). That is, return NULLs for the right-hand (outer) side, instead of removing rows from the left-hand (inner) side. – RBarryYoung Jul 09 '13 at 16:07

dynamic query not working properly

1 Answers1