Stored Proc that joins based on parameters??

Question

Post reply

Stored Proc that joins based on parameters??

stephenmbell

SSC Eights!

Points: 926
More actions
November 1, 2009 at 8:11 pm

#139828

In my database, I have about 8 or 9 stored procedures that select the same fields to populate a custom business object in my front end.
I have procs like:
GetLocationById
GetLocationsByRegion
GetLocationsByZip
GetActiveLocations
GetAllLocations
GetLocationsForDelivery
etc..
Recently, I had to add 3 fields to the Location table, and therefore, had to modify all of the procedures that selected from the Location table to populate my objects.
So I figured I would write 1 stored procedure to take the place of all of these other stored procs and based on the parameters I send in, it will determine what results I get back..
Parameters like:
@LocationId INT = NULL,
@ZipCode INT = NULL,
@Active BIT = True --most of teh time i am only after active locations
My confusion comes in when I pass a regionid in to get locations by region - when I do this I want to join the locations table with the locationsInRegions table.... or when I pass a deliveryId in, I want to join the locations table with the delivery table...
How is this normally done?
Thanks
sb

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

Either you pass the RegionID or the deliveryid are mandatory to your procedure, then you use the IF condition like

IF (@RegionID is not null) and (@deliveryid is null)

BEGIN

YOUR STATEMENTS

END

ELSE

IF (@RegionID null) and (@deliveryid is not null)

BEGIN

YOUR STATEMENTS

END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

stephenmbell (11/1/2009)
So I figured I would write 1 stored procedure to take the place of all of these other stored procs and based on the parameters I send in, it will determine what results I get back..
Parameters like:
@LocationId INT = NULL,
@ZipCode INT = NULL,
@Active BIT = True --most of teh time i am only after active locations

Careful...

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

stephenmbell SSC Eights! Points: 926 More actions · Answer 3

Thank you for the replies --

I understand this - about the inability to cache the execution plan - however, in my specific example, I am going to return a maximum 272 rows, and most of the time I will return atleast 265+...

That being said, as I stated earlier, I am looking for a way to accomplish combining a handful of stored procedures used to populate custom objects in my project. My problem is - if I add a property to my class that comes from the db, I have to modify all of these stored procedures (I am actually fixing this problem with a stored proc that was missed now)...

What is the best way to handle this?

Thanks,

sb

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Dynamic SQL would stop you fro having to update the SP. If you're unaware of the dangers of SQL injection, make sure you do some research on it before implementing this.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

stephenmbell SSC Eights! Points: 926 More actions · Answer 5

Thank you for your reply, I have read a little bit about dynamic SQL and the dangers of SQL Injection - and from what I have read, when using Dynamic SQL, it is inefficient because a cached execution plan can not be used.

How does this differ from the inefficiencies stated above using the multiple parameters defaulted to null?

Thanks,

sb

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Did you read through the blog posts that Gail linked? They explain why cached plans are not always a benefit for things like this.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

stephenmbell (11/3/2009)
and from what I have read, when using Dynamic SQL, it is inefficient because a cached execution plan can not be used.

Absolutely false. Dynamic SQL has its plans cached just like stored procedures do, retained in cache just as stored procedure plans are, reused if the same query runs again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aravind.s SSC-Addicted Points: 449 More actions · Answer 8

Hi

You can have just 2 parameters to Your Stored Procedure.

1)a flag

2)actual value.

Then You can build ur sp like

If flag =1 then the actual value will always contain id.

if flag=2 then the actual value will always contain location.

So based on ur first parameter you can retrieve all information You want.

But beware of the type conversions You will have to do if all ur input parameters (2nd one ) are not of the same type.