# Error : The statement did not return a result set

**URL:** <https://forum.inductiveautomation.com/t/error-the-statement-did-not-return-a-result-set/31820>\
**Category:** Ignition\
**Created:** [January 31, 2020, 4:00pm UTC](https://forum.inductiveautomation.com/t/error-the-statement-did-not-return-a-result-set/31820 "2020-01-31T16:00:31Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\
**Post date:** [January 31, 2020, 4:00pm UTC](https://forum.inductiveautomation.com/t/error-the-statement-did-not-return-a-result-set/31820/1 "2020-01-31T16:00:31Z")

</div>

I have a stored procedure as below,

```auto
ALTER PROCEDURE [dbo].[getLabTestDataBatchView]
	@startDate as Datetime,
	@endDate as Datetime

AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @Columns1 as VARCHAR(MAX)
	DECLARE @Columns2 as VARCHAR(MAX)
	DECLARE @columns3 as VARCHAR(MAX)

	SELECT	DISTINCT lton.order_number AS OrderNo,
							c.compound_name AS compoundName, 
							lt.batch_number AS batch_number,
							CASE WHEN lt.machine_number = 0 THEN m.name
								 ELSE ltn.test_machine
							END AS test_machine,
							CASE WHEN lt.pass_customer_specs = 1 THEN 'Pass'
								ELSE 'Fail'
							END AS passCustomer
						
	INTO #TEMP2
	FROM lab_test lt
	JOIN lab_test_compound ltc 
		ON ltc.id = lt.lab_test_compound_id
	JOIN compound c 
		ON c.id = ltc.compound_id
	LEFT JOIN lab_test_machine_configuration ltmc
		ON ltmc.id = ltc.lab_test_machine_configuration_id
	LEFT JOIN lab_test_machine ltn
		ON ltn.id = ltmc.lab_test_machine_id
	LEFT JOIN lab_test_measurement ltm 
		ON ltm.lab_test_machine_id = ltn.id
	LEFT JOIN measurement m
		ON m.id = ltm.measurement_id
	LEFT JOIN lab_test_order_number lton
		ON lton.lab_test_id = lt.id
	WHERE lt.last_test = 1 AND lt.date_created >= @startDate AND lt.date_created <= @endDate

	SELECT @Columns1 = COALESCE(@Columns1 + ', ','') + QUOTENAME(test_machine)
	FROM
		( SELECT DISTINCT test_machine
		  FROM #TEMP2) AS B
	ORDER BY B.test_machine

	;
	SET @columns3 = '	SELECT	compoundName, 
								OrderNo, 
								batch_number, 
								' + ISNULL(@Columns1,'''-'' AS [column]') +'
						FROM ( SELECT * 
								 FROM #TEMP2) AS PivotData
						PIVOT (MIN(passcustomer)
							FOR test_machine IN ('+ @Columns1 +')) AS pvt1 
						ORDER BY compoundName, batch_number'

	EXEC(@columns3)
	DROP TABLE #TEMP2
    -- Insert statements for procedure here
END

```

Everything runs fine when I call it from a table data binding as,

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/a/a8128987113148d8a36ec7b6acb7fcf4feffc9e3.png)

But, when there is no data available for a selected date it throws an exception as below rather than returning 0 dataset !

```auto
Exception: Error running query:
SQLQuery(query=Execute getLabTestDataBatchView '2020-01-31 00:00:00', '2020-01-31 23:59:59', database=LoMOS)@0ms
On: LabTestResultBatch_Temp.Root Container.DateRangeSelected.data
	caused by GatewayException: The statement did not return a result set.
	caused by SQLServerException: The statement did not return a result set.

Ignition v7.9.10 (b2018112821)
Java: Oracle Corporation 1.8.0_144

```

Any Ideas ?  
Thanks

---

<div class="post-metadata">

**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\
**Post date:** [February 4, 2020, 2:21pm UTC](https://forum.inductiveautomation.com/t/error-the-statement-did-not-return-a-result-set/31820/2 "2020-02-04T14:21:33Z")

</div>

Any suggestions ?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [February 4, 2020, 4:41pm UTC](https://forum.inductiveautomation.com/t/error-the-statement-did-not-return-a-result-set/31820/3 "2020-02-04T16:41:51Z")

</div>

The error is coming from the SQL Server JDBC driver, not Ignition. It’s telling you exactly what’s wrong.  
You could try just doing a UNION ALL with some dummy columns (with no rows) at the end of your stored procedure, so that even if your subquery doesn’t return anything you still return data with the right ‘shape’?

---

<div class="post-metadata">

**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\
**Post date:** [February 4, 2020, 6:02pm UTC](https://forum.inductiveautomation.com/t/error-the-statement-did-not-return-a-result-set/31820/4 "2020-02-04T18:02:26Z")

</div>

Ohh yeah, never thought of just passing NULL. Thanks so much !