CODEWITHSUNDEEP
sqlstored-proceduresdynamicsql-server-2005-express
JerseyGirl1201
JerseyGirl1201

Reputation: 11

dynamic query not working properly

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

Upvotes: 1

Views: 1488

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

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.

Upvotes: 2

Related Questions