question on how to create 1 new sp to call 2 other sp''s?
-
So if I want to pull data X from Sept. 2006 and compare the same data x to Sept. 2005 and calculate the difference.
Can I write two procedures with 2 date parameters in each SP and then create a new SP to call the 2 existing SP in?
If so, any tips on how to do it?
THanks
-
I will go with single procedure...
create proc abc
as
select * into #temp2005 from Sep2005table
select * into #temp2006 from Sep2006table
Calculate the difference from #temp2005 and #temp2006... these table will have subset of data it may be faster...
MohammedU
Microsoft SQL Server MVP -
Have a look at the script below. It works for additions, deletions and updates.
------------------------------------- Two sample tables
create table Sep2005(
lineID int,
linedata1 varchar(20),
linedata2 varchar(20),
)
create table Sep2006(
lineID int,
linedata1 varchar(20),
linedata2 varchar(20),
)
------------------------------------- Sample data
insert into Sep2005 values (1,'Only in SEP2005','One')
insert into Sep2005 values (2,'Same in both','Two')
insert into Sep2005 values (3,'Original','Three')
insert into Sep2006 values (2,'Same in both','Two')
insert into Sep2006 values (3,'Updated','33333')
insert into Sep2006 values (4,'Only in SEP2006','Four')
------------------------------------- Query to show altered, deleted or added rows
select 'SEP2005' as Source,* from
(select * from Sep2005
EXCEPT
select * from Sep2006) as S2005
UNION ALL
select 'SEP2006' as Source,* from
(select * from Sep2006
EXCEPT
select * from Sep2005) as S2006
-
Adding to what Mark posted above, this would show you the rows that were identical in both:
select * from Sep2006
INTERSECT
select * from Sep2005
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Hello all,
Thank you for your advices, but is everyone trying to tell me how to create two temp table , one is sep2006 and the other is sep2005?
What I think I need to do tho is a different.
I created a Stored Procedure to pull in 2005 info and I created another SP to pull in 2006 info.
Now I created a 3rd store procedure to call the 2 existing procedures in and merge the info from both sp's into 1 temp table in this 3rd stored procedure.
This is what my 3rd SP looks like, please tell me if I am on the right track. Thanks foryour help.
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
GO
Alter
proc [dbo].[rpt_payor_difference]
as
CREATE
TABLE #TEMPPAYOR
(
referral_05
INT,
CLIENTNAME_05
VARCHAR (32),
payor_05
VARCHAR (32),
saving05
decimal(5,2),
referral
INT,
CLIENTNAME
VARCHAR (32),
payor
VARCHAR (32),
saving
decimal(5,2)
)
INSERT
#TEMPPAYOR EXEC [dbo].[rpt_payor_Summary] '08/01/2005','09/01/2005'
INSERT
#TEMPPAYOR EXEC [dbo].[rpt_payor_Summary)2005] '08/01/2006','09/01/2006'
SELECT
* FROM #TEMPPAYOR
go
EXEC
[dbo].[rpt_payor_difference]
go
-
Hi Guys Good Efforts...
But why U are trying to split 2 temp tables. When we can do with simple one query. And doing this it will be faster because we'll not loose our Indexing also. Giving sample....
SELECT Code,
Description,
[Sep 2006] = A.Value,
[Sep 2007] = B.Value
FROM
(
SELECT Code
Time,
Description,
Value
FROM TableName
WHERE Time = 'Sep 2006'
) A
INNER JOIN
(
SELECT Code
Time,
Description,
Value
FROM TableName
WHERE Time = 'Sep 2007'
) B
ON A.Code = B.Code
And U can think about Pivot Function also in 2005.
-
And U can fit in your Sp also.
And for calling another sp into sp use EXEC SpName Perameters
-
Thanks Folks.
Here is my code- it works!! 🙂
SQL rocks!
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
GO
Alter
proc [dbo].[rpt_payor_difference]
as
Create
TABLE #TempPrevious
(
referral_05
INT,
CLIENTNAME_05
VARCHAR (32),
payor_05
VARCHAR (32),
saving05
decimal(5,2)
)
INSERT
#TempPrevious EXEC [dbo].[rpt_payor_Summary_2005] '08/01/2005','09/01/2005'
CREATE
TABLE #TempNow
(
referral
INT,
CLIENTNAME
VARCHAR (32),
payor
VARCHAR (32),
saving
decimal(5,2)
)
INSERT
#TempNow EXEC [dbo].[rpt_payor_Summary] '08/01/2006','09/01/2006'
SELECT
a
.referral,
a
.clientname,
a
.payor,
a
.saving,
b
.referral_05,
b
.clientname_05,
b
.payor_05,
b
.saving05,
sum
(a.saving) - sum(b.saving05) as 'NetSaving'
FROM
#TempNow as A
LEFT
OUTER JOIN
#TempPrevious
AS B ON a.clientname = b.clientname_05
Where
a.saving <> 0
Group
by a.referral, b.referral_05, a.payor, b.payor_05, a.clientname, b.clientname_05
,
a.saving, b.saving05
Order
by a.payor, b.payor_05, a.clientname, b.clientname_05
go
EXEC
[dbo].[rpt_payor_difference]
go
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply