Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1414
  • Last Modified:

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))

-- PURCHASED
INSERT INTO #tmp_sum EXEC spr_single_broker_history_purchases @brkr

-- OFFER ACCEPTED
INSERT INTO #tmp_sum EXEC spr_single_broker_history_offer_accepted @brkr

-- OFFER MADE
INSERT INTO #tmp_sum EXEC spr_single_broker_history_offer_made @brkr


SELECT * FROM #tmp_sum
GO


--  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

CREATE 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
GO


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

Thanks in advance.
-Jim
0
Jim Horn
Asked:
Jim Horn
  • 2
2 Solutions
 
wstuphCommented:
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
0
 
gad_flyCommented:
hi

just change this :

SELECT plcy_ref_no, plcy_ndb, plcy_status_date FROM #tmp
GO

to
this


SELECT plcy_ref_no, plcy_status_date , plcy_ndb FROM #tmp
GO

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


Mohammad Pourebtehaj
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Worked like a charm.  Thanks guys.  -Jim
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now