Insert stored procedure results into temp table

Question

I have a stored procedure that returns this result:

The way I call the stored procedure is:

Exec uspGetStandardUsingRoleandPhase '1908003'

I want to store these results into a temp table so I used insert into like this:

IF OBJECT_ID(N'tempdb.dbo.#tmp', N'U') IS NOT NULL
    DROP TABLE #tmp

CREATE TABLE #tmp
(
    startDate DATE,
    endDate DATE,
    strPhase NVARCHAR(50),
    strBadgeNumber NVARCHAR(30)
)

INSERT INTO #tmp (startDate, endDate, strPhase, strBadgeNumber)
    EXEC uspGetStandardUsingRoleandPhase '1908003'

But I get an error like this:

INSERT EXEC failed because the stored procedure altered the schema of the target table.

Well you have the error :) now you just need to find what is modifying the schema inside your SP. We can't help without the SP code. — Dale K
– Dale K, Commented Sep 26, 2019 at 0:21

squillman · Accepted Answer · 2019-09-26 01:45:39Z

23

Hard to say without seeing the code to the stored procedure, but my guess is that the procedure also creates a temp table named #tmp. Try creating a temp table with a different name and running your INSERT EXEC into that, or post the code to the procedure so we can see it.

answered Sep 26, 2019 at 1:45

squillman

13.7k3 gold badges45 silver badges65 bronze badges

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

2 Comments

Rajan Mishra Over a year ago

its a brilliant remark. I was using someone else's procedure that has the same name of temp table

Lewis Williams Over a year ago

solved my issue! i was using the same temp table name #Product thanks !!

Mark Sowul · Accepted Answer · 2020-04-18 19:47:22Z

1

Worth noting this can also come up in SQL 2016 if you're using Query Store, and it happens to run a clean up during the procedure's execution. They suggest increasing the Query Store size to reduce the likelihood, but other than that, they're not planning to fix it for SQL 2016 (it's fixed in 2017)

https://support.microsoft.com/en-us/help/4465511/error-556-insert-exec-failed-stored-procedure-altered-table-schema

answered Apr 18, 2020 at 19:47

Mark Sowul

10.7k1 gold badge49 silver badges52 bronze badges

1 Comment

Naomi Over a year ago

That article says that it's fixed in SP3 for SQL 2016 (which is what we're using and getting this error on occassion)

Collectives™ on Stack Overflow

Insert stored procedure results into temp table

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related