how to insert the output of a sotred proc into a table

Question

how to insert the output of a sotred proc into a table

srikar.ganti

SSC Rookie

Points: 47
More actions
August 23, 2010 at 9:10 am

#225059

Hi, I have a storedproc which prints a statement and I want that statement to be inserted into a table
ALTER PROC [dbo].[sp_GetMonitorParameters]
AS
DECLARE @Monitor_Name varchar(max)
DECLARE @Parameters varchar(max)
DECLARE @Value varchar(Max)
DECLARE @sql VARCHAR(MAX)
DECLARE C CURSOR FOR
select Monitor_ID,Monitor_Name, Parameters, Value
from tbl_monitoractions a, tbl_monitor_parameters p, tbl_monitors m
where a.ma_id = p.ma_id
and m.monitor_id = a.ma_monitor_id
OPEN C
FETCH NEXT FROM C
into @Monitor_Name, @Parameters, @Value
set @sql = @Monitor_Name + ' '
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = @sql + @Parameters + '=' + @Value + ', '
FETCH NEXT FROM C
into @Monitor_Name, @Parameters, @Value
END
set @sql = substring(@SQL,1,len(@SQL) -1)
exec(@Sql)
Print @sql
close c
deallocate c
EXEC sp_getmonitorparameters
O/P message: EXEC sp_Check_FileExist @Name_ID=99, @Contact_ID=88
I want the above message to be inserted into a table.
Can anyone please help me.
Thanks

Viewing 2 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

you have to define a table with the proper columns before you call the procedure.

if your proc, which is using dynamic sql, returns a different number of columns depending on your Parameters, i think you're going to have problems, and would only be able to use a kludge like openrowset for a procedure:

--example using sp_who2 into a table:

CREATE TABLE WHORESULTS (

[WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

[SPID] CHAR (5) NULL,

[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),

[STATUS] VARCHAR(30) NULL,

[LOGIN] VARCHAR(30) NULL,

[HOSTNAME] VARCHAR(30) NULL,

[BLKBY] VARCHAR(30) NULL,

[DBNAME] VARCHAR(30) NULL,

[COMMAND] VARCHAR(30) NULL,

[CPUTIME] INT NULL,

[DISKIO] INT NULL,

[LASTBATCH] VARCHAR(30) NULL,

[PROGRAMNAME] VARCHAR(200) NULL,

[SPIDINT] INT NULL

)

--table exists, insert some data

INSERT INTO WHORESULTS(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT)

EXEC sp_who2

if the table's dynamic, onlyl way i can think of is this, linking back too itself:

SELECT *

--into #temp

FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_YourDynamicProc)

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!