April 28, 2011 at 2:46 am
How to Join Two Stored Procedures
April 28, 2011 at 2:51 am
No Usha, you CANNOT join 1 SPs.. only tables and views can be joined..
April 28, 2011 at 2:54 am
DECLARE @StartYear INT
,@EndYear INT;
SET @StartYear = 2010
SET @EndYear = 2011
DECLARE @StartDate DATETIME
,@EndDate DATETIME
,@NumOfMonths INT
SELECT @StartDate = DATEADD(YYYY,(@StartYear -1900) ,0)
,@EndDate = DATEADD(YYYY,(@EndYear -1900) ,0)
,@NumOfMonths = ( @EndYear - @StartYear + 1 ) * 12
; WITH Tens (N) AS
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
)
, Hundreds (N) AS
(
SELECT T1.N FROM Tens T1 CROSS JOIN Tens T2
)
, Thousands (N) AS
(
SELECT T1.N FROM Hundreds T1 CROSS JOIN Hundreds T2
)
, NumbersTable(N) AS
(
SELECT 0
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM Thousands
)
SELECT DATEADD(MM, N,@StartDate) [Month]
FROM NumbersTable
WHERE N < @NumOfMonths
Is it possible to insert the values return by the above query into a table
April 28, 2011 at 3:00 am
Change
SELECT DATEADD(MM, N,@StartDate) [Month]
FROM NumbersTable
WHERE N < @NumOfMonths
to
SELECT DATEADD(MM, N,@StartDate) [Month]
INTO dbo.TempTable -- Add this line alone
FROM NumbersTable
WHERE N < @NumOfMonths
Look into the commented line 🙂
April 28, 2011 at 3:06 am
thanks ya
April 28, 2011 at 7:26 am
Hi,
You can apply join on the result set of two stored procedure. Following is an example showing the use of OPENQUERY for achieving this:
Syntax of OPENQUERY used in below SQL is:
OPENQUERY(<Server>,'<database name>.<Schema Name>.<Stored Procedure>')[/u]
Procedure 1:
CREATE PROCEDURE [dbo].[SP1]
AS
BEGIN
SELECT * FROM
[FoodMart 2008].INFORMATION_SCHEMA.COLUMNS
END
Procedure 2:
CREATE PROCEDURE [dbo].[SP2]
AS
BEGIN
SELECT * FROM
[FoodMart 2005].INFORMATION_SCHEMA.COLUMNS
END
Applying Join on above two stored Procedures:
SELECT A.TABLE_NAME, A.TABLE_CATALOG , B.TABLE_CATALOG
FROM
(SELECT * FROM
OPENQUERY ([INFO-DESK150],'[FOODMART 2008].DBO.SP1'))A
JOIN
(SELECT * FROM
OPENQUERY ([INFO-DESK150],'[FOODMART 2008].DBO.SP2'))B
ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
In case of any queries, feel free to reach me at rshankar@infocepts.com
Thanks and Regards,
Ramakant
---------------------------------------------------------------
Ramakant Shankar
InfoCepts | www.infocepts.com
Off: +91 712 224 5867 Ext 8388, +1 301 560 2591 Ext 8388
---------------------------------------------------------------
April 28, 2011 at 7:58 am
Hey thats a good call made by shankar Openquery will let you to use join condition.
OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider.
Thanks
Parthi
April 28, 2011 at 9:33 am
IMHO using OPENQUERY to join the resultsets of two procs is a hack. I have not looked into it, nor do I intend to, but I would venture a guess that employing the technique on a busy system will eventually lead to performance problems. I would recommend against using it for mainstream applications.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 28, 2011 at 11:49 am
I would use two temporary tables and insert into ... exec proc for both. This would give you the opportunity to tune the resultant query by apply indexes and assessing a valid QEP.
April 28, 2011 at 12:19 pm
That is good to know. Never even thought of trying this though.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply