CODEWITHSUNDEEP
sqlsql-servert-sql
Farukh
Farukh

Reputation: 2223

How to save stored procedure output into table

I am new to T-SQL so I apologize if I am stating my question wrong.

I have a SQL Server stored procedure that has properties and returns integer value also selects some columns from different tables.

I was wondering how can I store that result into regular table

DECLARE @prop1 int;
DECLARE @prop2 int;
DECLARE @result int;

set @prop1 = 2
set @prop2 = 5

exec @result = dbo.Proc1 @prop1    @prop2

Result:

id    name      value
---------------------
1     Example     6
2     Process     8
..    ........    ..

Thank you in advance for all your input.

//-----------Edit 04/22/2015---------------------------

I am trying to use T-SQL below: 

  SELECT * INTO Tx.ReportFacts FROM OPENROWSET('SQLNCLI',   'Server=GCA_A;Trusted_Connection=yes;',
   'SET NOCOUNT ON;SET FMTONLY OFF; EXEC @RC =   ReportLibrary.Rpt.Server_GetAllDiscrepancies @FacilityKey ,@StationKeys  ,@MedItemKeys ,@MedClassCodes ,@UserAccountKeys ,@ResolutionStatus  ,@TransactionLimit ,@ReportStartDate ,@ReportEndDate')
  -- Select Table
  SELECT *
  FROM Tx.ReportFacts;

But getting error below: 

OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@RC".

Can anyone point out why this is happening. And what is wrong with my T-SQL statement?

Upvotes: 1

Views: 864

Answers (1)

Iswanto San
Iswanto San

Reputation: 18569

You can use insert - exec.

Try this:

insert into table_name
exec dbo.Proc1 @prop1 @prop2;

Upvotes: 3

Related Questions