InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 14 Mar 2010 10:45:52 GMT20http://www.sqlservercentral.com/Forums/Topic812177-338-1.aspxHiYou can have just 2 parameters to Your Stored Procedure.1)a flag2)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.Tue, 03 Nov 2009 21:32:19 GMTaravind.shttp://www.sqlservercentral.com/Forums/Topic812177-338-1.aspx[quote][b]stephenmbell (11/3/2009)[/b][hr]and from what I have read, when using Dynamic SQL, it is inefficient because a cached execution plan can not be used.[/quote]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.Tue, 03 Nov 2009 16:41:59 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic812177-338-1.aspxDid you read through the blog posts that Gail linked? They explain why cached plans are not always a benefit for things like this.Tue, 03 Nov 2009 09:10:59 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic812177-338-1.aspxThank 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, sbTue, 03 Nov 2009 09:06:48 GMTstephenmbellhttp://www.sqlservercentral.com/Forums/Topic812177-338-1.aspxDynamic 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.Tue, 03 Nov 2009 07:14:10 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic812177-338-1.aspxThank 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,sbMon, 02 Nov 2009 18:35:23 GMTstephenmbellhttp://www.sqlservercentral.com/Forums/Topic812177-338-1.aspx[quote][b]stephenmbell (11/1/2009)[/b][hr]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[/quote]Careful...[url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url][url]http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]Sun, 01 Nov 2009 21:49:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic812177-338-1.aspxHi,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)BEGINYOUR STATEMENTSENDELSEIF (@RegionID null) and (@deliveryid is not null)BEGINYOUR STATEMENTSENDSun, 01 Nov 2009 20:28:08 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic812177-338-1.aspxIn 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:GetLocationByIdGetLocationsByRegionGetLocationsByZipGetActiveLocationsGetAllLocationsGetLocationsForDeliveryetc..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 locationsMy 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?ThankssbSun, 01 Nov 2009 20:11:12 GMTstephenmbell