How to Insert Stored Procedure Output Into Table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to Insert Stored Procedure Output Into Table?

Author

Topic

ddz
Starting Member

4 Posts

Posted - 2004-11-02 : 16:24:54

I am trying to take the results of a stored procedure and place those results in a new table. But the stored procedure that I created to do this seems to only run the query, but not create the table. (I don't get any error messages when testing it with SQL Query Analyzer.) What am I doing wrong?

Here's the code that is suppose to create a new table:

CREATE PROCEDURE [dbo].[CountHelpDeskResponsesTable] AS
CREATE TABLE #SumHelpDeskResponses
(
ContactID int,
Email varchar(50),
Form varchar(50),
Phone varchar(50),
Walk varchar(50)
)
INSERT INTO #SumHelpDeskResponses EXECUTE CountHelpDeskResponses
SELECT * FROM #SumHelpDeskResponses
DROP TABLE #SumHelpDeskResponses
GO

Here's the code from the stored procedure (that is generating a crosstab query):

CREATE PROCEDURE [dbo].[CountHelpDeskResponses] AS
SELECT ContactMethods.ContactID,
SUM(CASE ContactHelpDesk WHEN 'email' THEN 1 ELSE 0 END) AS Email,
SUM(CASE ContactHelpDesk WHEN 'form' THEN 1 ELSE 0 END) AS Form,
SUM(CASE ContactHelpDesk WHEN 'phone' THEN 1 ELSE 0 END) AS Phone,
SUM(CASE ContactHelpDesk WHEN 'walk' THEN 1 ELSE 0 END) AS Walk
FROM ContactMethods
GROUP BY ContactMethods.ContactID
GO

Here's what my table, ContactMethods, looks like:
(Dashes are used to preserve the spacing in this posting.)

ContactID- ContactHelpDesk
1 -------- email
2 -------- walk
3 -------- form
4 -------- phone
5 -------- email
6 -------- walk
7 -------- form

Here's what the results look like from the CountHelpDeskResponses stored procedure:

ContactID- Total of ContactStudent- email- form- phone- walk
1 -------- 1 ---------------------- 1
2 -------- 1 ------------------------------------------ 1
3 -------- 1 ----------------------------- 1
4 -------- 1 ----------------------------------- 1
5 -------- 1 ---------------------- 1
6 -------- 1 ------------------------------------------ 1
7 -------- 1 ----------------------------- 1

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-02 : 16:33:51

A temp table is dropped when the SP that created it ends. Cretae the temp table then call the sp to populate it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-02 : 16:37:34

Why are you doing this?

What do you mean seems only to run a query?

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-02 : 16:39:25

I don't see the point in using a proc that creates a temporary table, which only lives while the proc runs.
Just do your code:

CREATE TABLE #SumHelpDeskResponses
(
ContactID int,
Email varchar(50),
Form varchar(50),
Phone varchar(50),
Walk varchar(50)
)
INSERT INTO #SumHelpDeskResponses EXECUTE CountHelpDeskResponses
SELECT * FROM #SumHelpDeskResponses
DROP TABLE #SumHelpDeskResponses

But not within a sp.

while pouring coffee...
rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources