Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Creation of dynamic sql query to create a select statement to pick a unique record Expand / Collapse
Author
Message
Posted Thursday, August 9, 2012 5:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 18, 2014 12:54 PM
Points: 21, Visits: 155
Hi All

I am working on a search stored procedure for a website,

I have following input parameters:

@Region: A region name
@Country: A country name or collection of countries
@Province: A province name or collection of provinces
@city: A single city name
@Zip: A single zip code

I have table for each say Region, Country, Province and City(it contains both city and corresponding zip)
Now i want to pick a single record of city(there may be multiple identical records but want first one) to get it's latitude and longitude.
The best approach i thought of so far is to use dynamic sql query string.
But if there is another better way to do this then please suggest me.

Thanks
Post #1342527
Posted Thursday, August 9, 2012 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 1,395, Visits: 2,406
Why is a regular query not sufficient?
Is is unknown whether any of the given parameters will contain a value?
Post #1342592
Posted Thursday, August 9, 2012 10:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 18, 2014 12:54 PM
Points: 21, Visits: 155
Yes this is not sure whether any of parameter will contain value or not except @city or @zip.
and also I guess IN clause won't work with a variable containing multiple values

like the following code will not work:

select blah from blah where countryId IN (@country)
Post #1343125
Posted Thursday, August 9, 2012 10:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
Here is another pattern for "catch-all" queries:
 select *  
from t1
where (@p1 is null or @p1 =t1.c2)
option (recompile)

Not sure if better or not for your particular case, you'll want to benchmark it both ways.
Post #1343130
Posted Thursday, August 9, 2012 11:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
Rather than using a Dynamic SQL query that substitutes in the table you want to SELECT FROM, why not instead consider using IF/ELSE IF/END with static SQL queries?


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1343136
Posted Thursday, August 9, 2012 11:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 18, 2014 12:54 PM
Points: 21, Visits: 155

@dwain.c

I can Use IF/Else but there i'll have to use too many iterations,
Because @Country and @province may contain more that one value.
and each table City, Region, Country and Province contains a translation and also a synonym table.
It will make too many iterations.



@SpringTownDBA

In this solution how can i pass multiple values in parameter

declare @p1 varchar(100) = 'Evere,anvers'
select top 10 *
from TB_City ci
where (@p1 is null or @p1 =ci.CityDefaultName)
option (recompile)
Post #1343144
Posted Thursday, August 9, 2012 11:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
You can use a table valued parameter.

... And ( t1.c in (select id from @p2) or not exists (select * from @p2) )
Post #1343146
Posted Friday, August 10, 2012 1:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, May 26, 2014 10:11 AM
Points: 452, Visits: 820
rajawat.niranjan (8/9/2012)

@dwain.c

I can Use IF/Else but there i'll have to use too many iterations,
Because @Country and @province may contain more that one value.
and each table City, Region, Country and Province contains a translation and also a synonym table.
It will make too many iterations.



@SpringTownDBA

In this solution how can i pass multiple values in parameter

declare @p1 varchar(100) = 'Evere,anvers'
select top 10 *
from TB_City ci
where (@p1 is null or @p1 =ci.CityDefaultName)
option (recompile)


First of all, I think you should have posted a better subject to the question as it seems more likely the case of dynamic search ;)

I agree that IF/ELSE is not the way to go. It would make the code too long and would make it very hard for the optimizer to come up with a good plan unless the options like procedure recompile etc are used.

For multiple values in parameter, as mentioned by SpringTownDBA, TVP is an option. Another option could be to use Jeff Moden's 8k splitter function

But if you have opted for the dynamic sql, then it seems fine to me. (Although I do not like dynamic sql) I have seen proper implementation can get you a very optimized solution for dynamic searches. I would strongly recommend reading this great article by Erland Sommarskog on dynamic search.
Post #1343173
Posted Friday, August 10, 2012 2:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
Personally, I'd like to see the Dynamic SQL version posted before we go jumping to hasty conclusions or generalizations like "too many iterations" or "would make it very hard for the optimizer to come up with a good plan unless the options like procedure recompile" and compare it against what could be done with IF/ELSE IF.

Edit: Corrected the second quotation.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1343188
Posted Friday, August 10, 2012 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 4:34 AM
Points: 1, Visits: 7
Yes this is not sure whether any of parameter will contain value or not except @city or @zip.
and also I guess IN clause won't work with a variable containing multiple values

like the following code will not work:

select blah from blah where countryId IN (@country)


You can use the below query if a variable containing multiple values,
select blah from blah where
CHARINDEX(LTRIM(RTRIM(CountryId)), @country) > 0
Post #1343201
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse