June 24, 2015 at 12:39 am
Hello everyone,
is there a way to combine 2 stored procedures with a different set off parameters.
Basically my 1st stored procedure has the following parameters:
1.@PlanID
2.@FinancialYearID
3.@RangetypeID
My second stored proc has the following:
1.@FinancialYearID
2.@IndicatorID
3.@VersionID
i have researched and so far nothing seems to be working. There is a conflict between the FinancialYearID of the 1st and 2nd stored procs.
My overall result is the combination of the 1st and 2nd storedprocs in 1.
Please assist....
June 24, 2015 at 1:00 am
why you need to combine these to procedures in the 1st place ?
June 24, 2015 at 1:03 am
Basically our requirements is to put both the stored procs in 1.
We have been ordered to do so....:w00t:
June 24, 2015 at 1:34 am
Type of, pseudocode
create procedure newProc (
@Plan_Indicator_ID
@FinancialYearID,
@RangetypeID,
@VersionID
)
as
if (@VersionID is null and @RangetypeID is not null ) then
firstProcCode (@Plan_Indicator_ID,@FinancialYearID,@RangetypeID)
else if (@VersionID is not null and @RangetypeID is null ) then
secondProcCode (@FinancialYearID, @Plan_Indicator_ID, @VersionID)
else
throw ... --parameters exception
But i really can't understand how it makes the system better.
June 24, 2015 at 2:40 am
We have been ordered to do so....:w00t:
ok. thats a very good reason to merge two sp in 1.
i would suggest to create Calling SP and call your already existing sp from the new created one.
something like this.
Create Procedure SomeNewSP
(
@CallingParam, -------- determine which sp to execute from this parameter
@Param1,
@Param2,
@Param3,
)
AS
BEGIN
IF @CallingParam = 1
BEGIN
EXEC ExistintSP1 @Param1, @PARAM2
END
IF @CallingParam = 2
BEGIN
EXEC ExistintSP2 @Param2, @PARAM3
END
END
by doing this you do not need to change the old logic and get into the troubleshooting the single SP for all the combinations. and you will not loose any comments of history in your repository associated with the old sp
hope it helps.
June 24, 2015 at 3:03 am
Thanks Twin.
i have been working on something like your solution but i will make the tweeks to it now.
just one question though...
Will the financialYearID not conflict ?
June 24, 2015 at 3:10 am
Will the financialYearID not conflict ?
can share some detail regarding this, i didn't get your point on this one 🙂
June 24, 2015 at 3:13 am
twin.devil (6/24/2015)
We have been ordered to do so....:w00t:
ok. thats a very good reason to merge two sp in 1.
I disagree. The person doing the ordering may not know what they're talking about. If you can find out the reason for the requirement, it will help you either push back on it, or make sure that your eventual solution meets it as closely as possible.
John
June 24, 2015 at 3:14 am
i think you should first find out why someone would want to combine those two stored procs and base on that set some rules on what to do depending on the parameters provided or do something like twin.devil suggested to have an extra parameter to decide which sp gets executed.
June 24, 2015 at 3:23 am
here is working example which i have suggested. i also tried to give you an answer to conflict question. if it is you are looking for:
if object_id('Proc1') is not null
drop proc proc1
go
create proc proc1
(
@PlanIDint
, @FinancialYearIDint
, @RangetypeIDint
)
AS
begin
select @PlanID
, @FinancialYearID
, @RangetypeID
end
GO
------------ 2nd proc
if object_id('Proc2') is not null
drop proc proc2
go
create proc proc2
(
@FinancialYearIDint
, @IndicatorIDint
, @VersionIDint
)
AS
begin
select @FinancialYearID
, @IndicatorID
, @VersionID
end
GO
if object_id('newProc') is not null
drop proc newProc
go
create proc newProc
(
@CallingTypetinyint
, @PlanIDint
, @RangetypeIDint
, @FinancialYearIDint
, @IndicatorIDint
, @VersionIDint
)
as
begin
--------- @FinancialYearID will not conflict until the data is the same.
--- below code will not executed properly.
if @CallingType = 1
begin
exec proc1 @PlanID
, @FinancialYearID
, @RangetypeID
end
else if @CallingType = 2
begin
exec proc2 @FinancialYearID
, @IndicatorID
, @VersionID
end
end
GO
---------
exec newProc @CallingType= 1
, @PlanID= 1
, @RangetypeID= 1
, @FinancialYearID= 1
, @IndicatorID= null
, @VersionID= null
exec newProc @CallingType= 2
, @PlanID= null
, @RangetypeID= null
, @FinancialYearID= 1
, @IndicatorID= 2
, @VersionID= 2
---------- cleanup
if object_id('Proc1') is not null
drop proc proc1
go
if object_id('Proc2') is not null
drop proc proc2
go
if object_id('newProc') is not null
drop proc newProc
go
hope it helps.
June 24, 2015 at 3:28 am
John Mitchell-245523 (6/24/2015)
twin.devil (6/24/2015)
We have been ordered to do so....:w00t:
ok. thats a very good reason to merge two sp in 1.
I disagree. The person doing the ordering may not know what they're talking about. If you can find out the reason for the requirement, it will help you either push back on it, or make sure that your eventual solution meets it as closely as possible.
John
its was 'sarcastic' john. He have been ordered what else he can do about it 🙂
June 24, 2015 at 3:44 am
June 24, 2015 at 3:46 am
John Mitchell-245523 (6/24/2015)
Might be worth referring to Gail's article[/url] on catch-all queries.
The one on multiple execution paths[/url] may be more relevant.
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
June 25, 2015 at 11:02 am
compufreak (6/24/2015)
i have researched and so far nothing seems to be working. There is a conflict between the FinancialYearID of the 1st and 2nd stored procs.
What do you mean by "a conflict between the FinancialYearID"? Might there be two different FinancialYearIDs that need to be passed to the sproc?
Don Simpson
June 25, 2015 at 11:57 am
Let's take a close look at the info we have as well as what we don't:
1.) The two procs have differing parameter sets, which suggests strongly they have entirely different objectives, so there may be no good reason at all to combine them...
2.) Do the two procs return the same set of fields, or different sets of fields? If the latter is true, then what do you do? Which fields get returned, and under what circumstances? Have all the application calls to these two procs been identified, along with the subsequent code that might need to change if they now call a new proc? How do you decide which set of records to return, as just updating the code to use ALL the parameters for ALL the calls to it isn't sufficient. As a couple of posts with code clearly indicate, you'd need to add yet another parameter just to know which set of records to return.
3.) Are either of the two procs serving as the basis for any reports in SSRS or Crystal Reports or other similar reporting tools? Change the procedure and you'll be modifying all those reports as well.
4.) Are either of the two procs involved in any SSIS packages, or other ETL processes? Same problem here as in #3.
I could go on, but you get the idea...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply