Invalid Object when Calling Another Stored Procedure (Nesting?)

[Rock6431]

I'm new at writing these stored procedures, so bare with me... I am trying have one stored procedure call upon another, and am confused. I've read that you can have up to 32 stored proceedures within each other ect... But, I guess I'm doing it wrong. I have the following 2 SP's and how would I get #2 to call upon the first SP's results in order to get a count of the records within a specific age for #2. I have several other SQL selects just like #2 that I need to addon to this so that I can obtain a count of the various items based upon their ages...?


-----------------------------------------------
#1

CREATE PROCEDURE [dbo].[GetMyData_sp]
@ReportDate char (10),
@Market char (50),
@RepCenter char (50),
@RepTDL char (10),
@RepLname char (30)
AS
DECLARE @SQL varchar (1000)

SELECT @SQL = 'SELECT Exceptions, Status, ReportDate, ExceptionDate ' +
' Market, RepCenter, RepTDL, RepLname, Age ' +
' FROM [dbo].data ' +
' WHERE (ReportDate = ''' + @ReportDate + ''') '

IF @Market <> NULL
BEGIN
SELECT @SQL = @SQL + ' AND (Market = ''' + @Market + ''') '
END

IF @RepCenter <> NULL
BEGIN
SELECT @SQL + @SQL + 'AND (RepCenter = ''' + @RepCenter + ''') '
END

IF @RepTDL <> NULL
BEGIN
SELECT @SQL = @SQL + 'AND (RepTDL = ''' + @RepTDL + ''') '
END

IF @RepLname <> NULL
BEGIN
SELECT @SQL = @SQL + 'AND (RepLname = ''' + @RepLname + ''') '
END

SELECT @SQL = @SQL + 'GROUP BY RepCenter, RepTDL, RepLname, ReportDate, Market, Exceptions, ExceptionDate, Status, AGE '

EXEC (@SQL)
GO
-------------------------------------------------------

#2
CREATE PROCEDURE [dbo].[GetAGE1_sp]

AS

SELECT Exceptions, Status, Count(Status) AS AGE1
FROM GetMyData_sp
WHERE (Age Between 1 and 2)
GROUP BY Exceptions, Status
GO

-------------------------------------------------------

 

[cjowsey]

I don't know if I can solve all your problems at once, but here's my twobob's worth.

Firstly, a stored procedure returns a result set. It doesn't normally store data on which you can perform further selection criteria. If you just want to limit your selection by the age range, then put the clause &quot;WHERE (Age Between 1 and 2)&quot; into your first stored procedure.

Secondly, have a good look at the SELECT statement in BOL or any SQL textbook. I suspect that you want an &quot;ORDER BY&quot; rather than a &quot;GROUP BY&quot; clause.

Thirdly, if your aim is to do further selection, grouping or ordering on the data you are selecting, consider turning your stored procedure into a view and select from the view rather than the base tables. Views are very powerful tools in many ways. It took me far too long to recognise the benefits of them when I first started using SQL.

Finally, as an extension to what I said in the first point, it is quite easy to store a set of intermediate results for later use. Depending on how long you want to keep the results, you can amend your first stored procedure to select into either a permanent or a temporary table. Your subsequent stored procedures can select from this table. Without really knowing your requirements, I suspect that you don't need to do this at the moment.

Regards,
Clive

 

[Rock6431]

Clive,

Thanks for the advice. I think I need to create a temporary table for the info from #1 but I don't know how to get the data or result set from the @SQL over into a TempTable with the same column names as that of the Original table, so that I can parse the remaining functions to the Temp table as opposed to working with views at this point. How to put these together? I've been reading a couple of sites and it's not too easy to see at this point. I think what I am having probs with is what variable is my output from #1 and how do I feed it into the Table... ARRRRGGGHHH....


EXEC (@SQL)

Create Table #PivotTemp(output varchar(8000))
INSERT #
SELECT * FROM #PivotTemp


Thanks again for the advise.
Rock6431

 

[cjowsey]

Rock6431,

Check out SELECT ... INTO in BOL. This will allow you to create a table with the same columns that you are selecting in #1. All you should need to do (famous last words) is modify your dynamic SQL statement in @SQL to use the INTO clause.

You also might want to have a look at threads 183-105243 and 183-300964 in this forum or do a search on temporary table.

Regards,
Clive

 

[tlbroadbent]

You can insert the results of the first stored procedure into a temp tablewith the following syntax.

CREATE PROCEDURE [dbo].[GetAGE1_sp] AS

Create table #MyData
(Exceptions <data type>,
Status <data type>,
ReportDate <data type>,
ExceptionDate <data type>,
Market <data type>,
RepCenter <data type>,
RepTDL <data type>,
RepLname <data type>,
Age <data type>)

Insert #mydata
Exec dbo.GetMyData_sp
@ReportDate=<ReportDate>,
@Market=<market>,
@RepCenter=<repcenter>,
@RepTDL=<reptdl>,
@RepLname=<replname>

SELECT Exceptions, Status, Count(Status) AS AGE1
FROM #mydata
WHERE (Age Between 1 and 2)
GROUP BY Exceptions, Status
GO
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Rock6431]

Thanks Guys for the info. I am just trying to take a lick at using a stored procedure to perform this task instead. I have been reading the BOL at the MS site along with a couple others and sometimes it just helps to have someone who speaks plainly.

I think I got this almost done, but now I am running into an error which is puzzling. I am getting

ERROR: 2714: There is already an object named'##MyPivo' in the database.

Since I got this, I added the routines to remove it when ran and at the end, but I can't save the new SP since the Table object already exists. How/Where do I find it, so I can delete it. I have looked through the server to the best of my knowledge and I can't locate it.


/* +++++++++PIVOT MAIN ++++++++++++++ */
/* +++++++++Get Data SQL Conversion++++++++++++++ */

CREATE PROC dbo.[GetPivot]
@inReportDate nvarchar (10),
@inMarket nvarchar (50),
@inRepCenter nvarchar (255),
@inRepTDL nvarchar (255),
@inRepLname nvarchar (255)
AS

IF EXISTS
(SELECT *
FROM TEMPDB..SYSOBJECTS
WHERE NAME = '##MyPivo')
DROP TABLE #MyPivo
GO


/* ++++++++++++++++++++++++++++++++++++
Create a Temp Table for PIVOT data to be
worked with-from for the various queries
++++++++++++++++++++++++++++++++++++ */

Create table ##MyPivo
(Exceptions nvarchar (255),
Status nvarchar (255),
ReportDate nvarchar (10),
ExceptionDate smalldatetime,
Market nvarchar (50),
RepCenter nvarchar (255),
RepTDL nvarchar (255),
RepLname nvarchar (255),
Age numeric (5))

Insert ##MyPivo
Exec dbo.GetMyData_sp
@ReportDate=inReportDate,
@Market=inMarket,
@RepCenter=inRepCenter,
@RepTDL=inRepTDL,
@RepLname=inRepLname

SELECT Exceptions, Status, Count(Status) AS AGE1
FROM ##MyPivo
WHERE (Age Between 1 and 2)
GROUP BY Exceptions, Status
GO
SELECT Exceptions, Status, Count(Status) AS AGE2
FROM ##MyPivo
WHERE (Age Between 3 and 4)
GROUP BY Exceptions, Status
GO
SELECT Exceptions, Status, Count(Status) AS AGE3
FROM ##MyPivo
WHERE (Age Between 5 and 6)
GROUP BY Exceptions, Status
GO
SELECT Exceptions, Status, Count(Status) AS AGE4
FROM ##MyPivo
WHERE (Age Between 7 and 9)
GROUP BY Exceptions, Status
GO
SELECT Exceptions, Status, Count(Status) AS AGE5
FROM ##MyPivo
WHERE (Age Between 10 and 30)
GROUP BY Exceptions, Status
GO
SELECT Exceptions, Status, Count(Status) AS AGE6
FROM ##MyPivo
WHERE (Age Between 31 and 60)
GROUP BY Exceptions, Status
GO
SELECT Exceptions, Status, Count(Status) AS AGE7
FROM ##MyPivo
WHERE (Age > 61)
GROUP BY Exceptions, Status
GO

SELECT #MyPivo.Exceptions, #MyPivo.Status, Age1, Age2, Age3,
Age4, Age5, Age6, Age7
FROM Age2 RIGHT OUTER JOIN
#MyPivo LEFT OUTER JOIN
Age7 ON #MyPivo.Status = Age7.Status AND #MyPivo.Exceptions = Age7.Exceptions LEFT OUTER JOIN
Age6 ON #MyPivo.Status = Age6.Status AND #MyPivo.Exceptions = Age6.Exceptions LEFT OUTER JOIN
Age5 ON #MyPivo.Exceptions = Age5.Exceptions AND #MyPivo.Status = Age5.Status LEFT OUTER JOIN
Age4 ON #MyPivo.Exceptions = Age4.Exceptions AND #MyPivo.Status = Age4.Status LEFT OUTER JOIN
Age3 ON #MyPivo.Status = Age3.Status AND #MyPivo.Exceptions = Age3.Exceptions LEFT OUTER JOIN
Age1 ON #MyPivo.Exceptions = Age1.Exceptions AND #MyPivo.Status = Age1.Status ON
Age2.Exceptions = #MyPivot.Exceptions AND Age2.Status = #MyPivo.Status
GROUP BY Age1, Age2, Age3, Age4, Age5, Age6,
Age7, #MyPivo.Exceptions, #MyPivo.Status
GO


DROP TABLE ##MyPivo
GO

 

[SQLSister]

IF EXISTS
(SELECT *
FROM TEMPDB..SYSOBJECTS
WHERE NAME = '##MyPivo')
DROP TABLE #MyPivo
GO

Note you are not dropping the same table that you are checking for existence. Why are you doing this as a global table anyway? In fact it looks like you go back and forth between global and local temp tables throughout. Need to be consistent. I see no real reason why it should be global, so you could use the #MyPivo through the whole thing instead of ##MyPivo