SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creation of dynamic sql query to create a select statement to pick a unique record


Creation of dynamic sql query to create a select statement to pick a unique record

Author
Message
rajawat.niranjan
rajawat.niranjan
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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
sestell1
sestell1
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3524 Visits: 3508
Why is a regular query not sufficient?
Is is unknown whether any of the given parameters will contain a value?
rajawat.niranjan
rajawat.niranjan
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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)
SpringTownDBA
SpringTownDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 1499
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.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17661 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
rajawat.niranjan
rajawat.niranjan
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 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)
SpringTownDBA
SpringTownDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 1499
You can use a table valued parameter.

... And ( t1.c in (select id from @p2) or not exists (select * from @p2) )
Usman Butt
Usman Butt
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 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 Wink

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.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17661 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
selvaramanp
selvaramanp
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search