INSERT INTO {temp table} EXEC {stored procedure} {parameter} not working

Hello SQL Experts

I'm trying to build a SP that grabs the recordsets from three other SP's, and am getting the following error message:

Server: Msg 260, Level 16, State 1, Procedure spr_single_broker_history_purchases, Line 47
Disallowed implicit conversion from data type datetime to data type decimal, table 'tempdb.dbo.#tmp_sum____________________________________________________________________________________________________________0000000E0E36', column 'plcy_ndb'. Use the CONVERT function to run this query.

-- Below is my SP in-progress that calls three separate SP's and INSERTS them into one table

declare @brkr int
Set @brkr = 18

IF OBJECT_ID('tempdb..#tmp_sum') IS NOT NULL
      drop table #tmp_sum

CREATE TABLE #tmp_sum (
      plcy_ref_no varchar(15),
      plcy_status_date datetime,
      plcy_ndb decimal(19,4))

INSERT INTO #tmp_sum EXEC spr_single_broker_history_purchases @brkr

INSERT INTO #tmp_sum EXEC spr_single_broker_history_offer_accepted @brkr

INSERT INTO #tmp_sum EXEC spr_single_broker_history_offer_made @brkr

SELECT * FROM #tmp_sum

--  Below is one of the calling SP's.  The other two are formatted exactly the same way.
--  All three SP's have permissions for all users to execute.

CREATE PROCEDURE spr_single_broker_history_purchases (@brkr_id int) AS

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
      drop table #tmp

      plcy_id int,
      plcy_ref_no varchar(15),
      plcy_status_date datetime,
      plcy_ndb decimal(19,4))

-- Code deleted, works fine

SELECT plcy_ref_no, plcy_ndb, plcy_status_date FROM #tmp

-- The following, when run in Query Analyzer, works just fine
EXEC spr_single_broker_history_purchases 18

Thanks in advance.
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try inserting in the same order you create the tables:

SELECT plcy_ref_no, plcy_ndb, plcy_status_date FROM #tmp

should be

SELECT plcy_ref_no, plcy_status_date, plcy_ndb FROM #tmp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

just change this :

SELECT plcy_ref_no, plcy_ndb, plcy_status_date FROM #tmp


SELECT plcy_ref_no, plcy_status_date , plcy_ndb FROM #tmp

!! when i read your Q  wstuph  answer that :))

Mohammad Pourebtehaj
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Worked like a charm.  Thanks guys.  -Jim
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.