Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Creation of dynamic sql query to create a...
Add to briefcase
17 posts, Page 1 of 2
12»»

Creation of dynamic sql query to create a select statement to pick a unique record Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
rajawat.niranjan
Posted Thursday, August 09, 2012 5:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:31 AM
Points: 21, Visits: 122
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
sestell1
Posted Thursday, August 09, 2012 6:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:36 AM
Points: 837, Visits: 1,219
Why is a regular query not sufficient?
Is is unknown whether any of the given parameters will contain a value?
Post #1342592
rajawat.niranjan
Posted Thursday, August 09, 2012 10:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:31 AM
Points: 21, Visits: 122
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
SpringTownDBA
Posted Thursday, August 09, 2012 10:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 316, Visits: 1,484
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
dwain.c
Posted Thursday, August 09, 2012 11:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192
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?

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1343136
rajawat.niranjan
Posted Thursday, August 09, 2012 11:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:31 AM
Points: 21, Visits: 122

@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
SpringTownDBA
Posted Thursday, August 09, 2012 11:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 316, Visits: 1,484
You can use a table valued parameter.

... And ( t1.c in (select id from @p2) or not exists (select * from @p2) )
Post #1343146
Usman Butt
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: Thursday, March 14, 2013 11:00 AM
Points: 444, Visits: 807
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
dwain.c
Posted Friday, August 10, 2012 2:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192
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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1343188
selvaramanp
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
17 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.