0

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

  • You are connecting to SQLServer? Is this code a stored procedure? – June7 Jun 03 '20 at 00:22
  • Hi John , when you are giving values from other cells , do check on single quotes – Ashish Shetkar Jun 03 '20 at 12:22
  • I am connecting to a SQL Server. I'm not using a stored procedure. I'm using a connection in Excel that would normally allow the use of parameters using a ?. It runs fine when I hard code the dates but as soon as I change the dates to use a ? I get the error. I don't get past changing the dates to parameters to be able to define the parameter cells to use. – John Self Jun 04 '20 at 18:27

0 Answers0