Select * into #tempTable from exec Command shows an error [duplicate]

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
– Farrokh, Commented Jul 24, 2014 at 5: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
– jai, Commented Jul 24, 2014 at 5:17

Community · Accepted Answer · 2017-05-23 12:15:07Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered Jul 24, 2014 at 5:05

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jai Over a year ago

spnotification will produce dynamic data.In our environment openQuery has been restricted!

Gidil · Accepted Answer · 2014-07-24 07:02:03Z

0

Try this:

DECLARE @Testing TABLE 
  ( 
     COLUMN1 INT, 
     COLUMN2 INT 
  ) 

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

edited Jul 24, 2014 at 7:02

Gidil

4,1472 gold badges38 silver badges50 bronze badges

answered Jul 24, 2014 at 5:07

mehdi lotfi

11.6k18 gold badges85 silver badges128 bronze badges

Collectives™ on Stack Overflow

Select * into #tempTable from exec Command shows an error [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related