How to insert procedure output in table?

Question

How to insert procedure output in table?

Pravin Patel-491467

Say Hey Kid

Points: 674
More actions
June 1, 2010 at 5:30 am

#222139

Hi Friends,
I found an error while try to insert ''EXEC GetFileSpaceStats 1'' result in table. My procedure is as below.
---------------------------------------------------------------------------------------------
CREATE PROCEDURE Shrink_Drive_Space (@Drive_Latter CHAR(1))
AS
CREATE TABLE #Space_Table (RowID INT, Server_Name VARCHAR(100), DBName VARCHAR(100), Flag BIT, FileID INT,
File_Group VARCHAR(100), Total_Space FLOAT, UsedSpace FLOAT, FreeSpace FLOAT, FreePct FLOAT,
Name VARCHAR(1000),[FileName] VARCHAR(8000), Report_Date DATETIME)
EXEC ('
INSERT INTO #Space_Table (RowID, Server_Name, DBName, Flag, FileID, File_Group, Total_Space,
UsedSpace, FreeSpace, FreePct, Name, [FileName], Report_Date)
EXEC (''EXEC GetFileSpaceStats 1'')')
BEGIN
SELECT * FROM #Space_Table WHERE [FileName] LIKE ''+@Drive_Latter+'%'
END
---------------------------------------------------------------------------------------------
I got below error MSG:
Msg 8164, Level 16, State 1, Procedure GetFileSpaceStats, Line 91
An INSERT EXEC statement cannot be nested.
I am using SQL Server 2008. I try to find solutions on google. It suggest me to create user data type. i also tried with user data type Though i got same error.
Can anybody please help me!!!.....
Thanks & Regards,
Pravin Patel.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 1

Try this query

INSERT INTO #Space_Table (RowID, Server_Name, DBName, Flag, FileID, File_Group, Total_Space, UsedSpace, FreeSpace, FreePct, Name, [FileName], Report_Date)

EXEC GetFileSpaceStats 1

Nag

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Pravin Patel-491467 Say Hey Kid Points: 674 More actions · Answer 2

Pravin Patel-491467

Say Hey Kid

Points: 674

June 1, 2010 at 6:34 am

#1174734

Sorry Dear, but this is also not working.

Lowell SSC Guru Points: 323482 More actions · Answer 3

Pravin Patel-491467 (6/1/2010)
Sorry Dear, but this is also not working.

well this compiles correctly, but it depends on the other procedure GetFileSpaceStats , which was not posted so far;

if the procedure produces the same columns as the defined table, i should work fine, Just as Nag implied:

CREATE PROCEDURE Shrink_Drive_Space (@Drive_Latter CHAR(1))

AS

CREATE TABLE #Space_Table (RowID INT, Server_Name VARCHAR(100), DBName VARCHAR(100), Flag BIT, FileID INT,

File_Group VARCHAR(100), Total_Space FLOAT, UsedSpace FLOAT, FreeSpace FLOAT, FreePct FLOAT,

Name VARCHAR(1000),[FileName] VARCHAR(8000), Report_Date DATETIME)

--Try this query

INSERT INTO #Space_Table (RowID, Server_Name, DBName, Flag, FileID, File_Group, Total_Space, UsedSpace, FreeSpace, FreePct, Name, [FileName], Report_Date)

EXEC GetFileSpaceStats 1

BEGIN

SELECT * FROM #Space_Table WHERE [FileName] LIKE ''+@Drive_Latter+'%'

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150457 More actions · Answer 4

You are not the first person to want to record volume free space. There are a number of articles and scripts on this site:

http://qa.sqlservercentral.com/search/?q=free+space&t=a&t=s

There are also better ways to share data between procedures:

How to share data between stored procedures

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi