Select * into #tempTable from exec Command shows an error

Question

I am executing the string that contains the below code

DECLARE @BadgeNo NVARCHAR(MAX);
SET @BadgeNo='8107';

SELECT * INTO #Testing 
   EXEC spNotification 'Param1','Param2','Param3','Param4';

EXEC  spSqlTmpTblToHtmlTbl 'tempdb..#Testing'

I just want the result in html format. So I am executing spnotification to get the results.

The spSqlTmpTblToHtmlTbl converts the temp table to table format. But here is an issue that I can't create a temp table from spNotification result. I know

select * into

command won't work with the exec command. So how can I achieve this?

change your spNotification to a table value function and then use it — Farrokh, Jul 24 '14 at 05:03
http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure — shree.pat18, Jul 24 '14 at 05:03
I already have to Stored Procedure and we are trying to use that stored procedure globally. Incase if we have to make any modification in the logic we have to alter the SP alone. Also function takes much time to compile and execute. — jai, Jul 24 '14 at 05:17

score 3 · Accepted Answer · edited May 23 '17 at 12:15

3

You probably want to do something like this":

CREATE TABLE #Testing 
(
   COLUMN1 INT,
   COLUMN2 INT
)

INSERT INTO #Testing 
Exec spNotification 'Param1','Param2','Param3','Param4';

Also check How to SELECT * INTO [temp table] FROM [stored procedure]

Or you may try with OPENQUERY:

SELECT  *
INTO    #Testing 
FROM    OPENQUERY(YOURSERVERNAME, 'Exec spNotification 'Param1','Param2','Param3','Param4'')

edited May 23 '17 at 12:15

Community

1
1

answered Jul 24 '14 at 05:05

Rahul Tripathi

168,305
31
280
331

spnotification will produce dynamic data.In our environment openQuery has been restricted! – jai Jul 24 '14 at 05:18

score 0 · Answer 2 · edited Jul 24 '14 at 07:02

0

Try this:

DECLARE @Testing TABLE 
  ( 
     COLUMN1 INT, 
     COLUMN2 INT 
  ) 

INSERT INTO @Testing 
            (COLUMN1, 
             COLUMN2) 
EXEC SPNOTIFICATION 
  'Param1', 
  'Param2', 
  'Param3', 
  'Param4';

edited Jul 24 '14 at 07:02

Gidil

4,137
2
34
50

answered Jul 24 '14 at 05:07

mehdi lotfi

11,194
18
82
128

Select * into #tempTable from exec Command shows an error

2 Answers2