When I create a connection to SQL in Excel that has a temp table for a lookup and I hard code the parameters for the SQL query, everything runs fine. When I change the hard coded parameters to ? to be able to use dynamic parameters from a cell, I get an Invalid Object Name error on the Temp Table. ANy help is greatly appreciated.
SET NOCOUNT ON
CREATE TABLE #ACTUALPENDCODESLIST
(ID INT IDENTITY(1,1) PRIMARY KEY,
PENDCODE CHAR(2),
PENDDESCRIPTION VARCHAR(255),
AREAOFOWNERSHIP VARCHAR(255),
PendQueue VARCHAR(255),
PendPriority INT)
INSERT INTO #ACTUALPENDCODESLIST VALUES ('02','PEND - PHYSICIAN/GROUP NOT CONTRACTED FOR RENAL CARE SERVICES','N/A','Not Applicable',999)
INSERT INTO #ACTUALPENDCODESLIST VALUES ('03','PEND - PROC DETAIL NOT FOUND ON FILE','Claims','MCLAIM/HCLAIM',267)
SELECT DISTINCT(SUBSTRING(ph.FREE_FORM_DATA, 16,2)) AS 'Pend Code',
apc.PENDDESCRIPTION AS 'Description',
COUNT(DISTINCT(ph.CLAIM_NBR)) AS 'Count'
FROM process_hist ph
INNER JOIN #ACTUALPENDCODESLIST apc
ON SUBSTRING(ph.FREE_FORM_DATA, 16,2) = apc.PENDCODE
WHERE ph.YMDTRANS BETWEEN ? AND ?
AND ph.OP_NBR NOT LIKE 'SYS%'
AND ph.OP_NBR NOT LIKE 'SMK%'
AND SUBSTRING(ph.FREE_FORM_DATA, 13,2) = 'EX'
AND SUBSTRING(ph.FREE_FORM_DATA, 16,2) IN (SELECT PENDCODE FROM #ACTUALPENDCODESLIST)
GROUP BY SUBSTRING(ph.FREE_FORM_DATA, 16,2), apc.PENDDESCRIPTION
ORDER BY [Count] DESC, [Pend Code]
DROP TABLE #ACTUALPENDCODESLIST```