|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 9:01 AM
Points: 6,
Visits: 11
|
|
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
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 807,
Visits: 2,028
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 14,804,
Visits: 10,597
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 9:01 AM
Points: 6,
Visits: 11
|
|
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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 1,106,
Visits: 2,782
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 9:01 AM
Points: 6,
Visits: 11
|
|
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
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 1,106,
Visits: 2,782
|
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 14,804,
Visits: 10,597
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, November 16, 2009 11:11 PM
Points: 11,
Visits: 19
|
|
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.
|
|
|
|
