# Stored procedure not returning a result set?

**URL:** <https://forum.inductiveautomation.com/t/stored-procedure-not-returning-a-result-set/3261>\
**Category:** Ignition\
**Created:** [September 7, 2011, 5:17pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-not-returning-a-result-set/3261 "2011-09-07T17:17:09Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![adamstagnaro](https://avatars.discourse-cdn.com/v4/letter/a/919ad9/32.png) [@adamstagnaro](https://forum.inductiveautomation.com/u/adamstagnaro)\
**Post date:** [September 7, 2011, 5:17pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-not-returning-a-result-set/3261/1 "2011-09-07T17:17:09Z")

</div>

Hi there,

I have a stored procedure call from a table in my Ignition program. I have done this for other tables and had positive results. The stored procedure in this instance works when I execute it in MS SQL Server (2008), but not from Ignition. I get a “Gateway Error 500: The statement did not return a result set.” Note, the @MonthName variable isn’t being used yet… I haven’t added the logic to do month filtering yet.

This is the query in the data binding:

[dbo].[usp\_MonthlySOAMetrics] {Root Container.Container 3.Month\_SOA.selectedValue}

This is the contents of the procedure which pulls together data from several tables:

@MonthName varchar(15)  
WITH EXEC AS CALLER  
AS  
CREATE TABLE #Metrics([ShiftName] VARCHAR(12) ,[t\_stamp] DATETIME, [DownTime] INT, [Available\_Time] INT, [Goal] INT, [Parts] INT,[Day\_of\_the\_week] VARCHAR(12), PRIMARY KEY(ShiftName,t\_stamp))

INSERT INTO #METRICS  
SELECT ‘FORX\_DAY’, [t\_stamp], [DownTime], [Available\_Time], [Goal], [Parts], [Day\_of\_the\_week] from Metrics\_FORX\_Day —WHERE CONVERT(VARCHAR(12), DATEADD(DD, 0,t\_stamp), 101) = CONVERT(VARCHAR(12), SOA\_Summary.ForDate, 101)  
UNION  
SELECT ‘FORX\_SWING’, [t\_stamp], [DownTime], [Available\_Time], [Goal], [Parts], [Day\_of\_the\_week] from Metrics\_FORX\_Swing  
UNION  
SELECT ‘ES\_DAY’, [t\_stamp], [DownTime], [Available\_Time], [Goal], [Parts], [Day\_of\_the\_week] from Metrics\_ES\_Day  
UNION  
SELECT ‘ES\_SWING’, [t\_stamp], [DownTime], [Available\_Time], [Goal], [Parts], [Day\_of\_the\_week] from Metrics\_ES\_Swing  
UNION  
SELECT ‘PSD\_DAY’, [t\_stamp], [DownTime], [Available\_Time], [Goal], [Parts], [Day\_of\_the\_week] from Metrics\_PSD\_Day  
UNION  
SELECT ‘PSD\_SWING’, [t\_stamp], [DownTime], [Available\_Time], [Goal], [Parts], [Day\_of\_the\_week] from Metrics\_PSD\_Swing  
UNION  
SELECT ‘SHOX\_DAY’, [t\_stamp], [DownTime], [Available\_Time], [Goal], [Parts], [Day\_of\_the\_week] from Metrics\_SHOX\_Day  
UNION  
SELECT ‘SHOX\_SWING’, [t\_stamp], [DownTime], [Available\_Time], [Goal], [Parts], [Day\_of\_the\_week] from Metrics\_SHOX\_Swing

SELECT  
SOA\_Summary.ShiftName,  
SUM(CONVERT(DECIMAL,(STR(ISNULL(StandardHours, 0), 10, 2)))) AS [Standard Hours],  
SUM(CONVERT(DECIMAL,(STR(ISNULL(ActualHours, 0), 10, 2)))) AS [Actual Hours],  
STR(AVG(CONVERT(DECIMAL,(ISNULL(SOA, 0)))),10,2) AS [% SOA]  
FROM SOA\_Summary INNER JOIN #METRICS ON CONVERT(VARCHAR(12), DATEADD(DD, 0,t\_stamp), 101) = CONVERT(VARCHAR(12), SOA\_Summary.ForDate, 101) AND #Metrics.ShiftName = SOA\_Summary.ShiftName  
WHERE  
(t\_stamp \>= DATEADD(MONTH, - 1, GETDATE()-1)) AND (t\_stamp \<= GETDATE() + 1)  
GROUP BY SOA\_Summary.ShiftName

Is there something here that is not compatible with Ignition? My other stored procedures work flawlessly.

Thanks!

Adam S

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [September 7, 2011, 5:24pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-not-returning-a-result-set/3261/2 "2011-09-07T17:24:32Z")

</div>

Where are you executing this from? Wherever it is, it expects the statement to be a “select” statement, instead of something that only updates the db (and doesn’t return anything).

If you’re using other procedures in this way, it’s likely they return a result (even if it’s just the RowCount [rows affected]), which keeps it happy.

The solution will depend on where it is you’re trying to call this from. If you can call it from a script, use the “system.db. runUpdateQuery” function. If from a SQLTag, you can use the expression function “executeUpdateQuery”. If in a normal expression binding, you may need to use the scripting function, wrapped in the “runScript” expression function.

Probably more than a bit confusing, but I hope this helps a bit!

Regards,

---

<div class="post-metadata">

**Author:** ![adamstagnaro](https://avatars.discourse-cdn.com/v4/letter/a/919ad9/32.png) [@adamstagnaro](https://forum.inductiveautomation.com/u/adamstagnaro)\
**Post date:** [September 7, 2011, 5:29pm UTC](https://forum.inductiveautomation.com/t/stored-procedure-not-returning-a-result-set/3261/3 "2011-09-07T17:29:14Z")

</div>

I found another topic in here " Running Totals / SQL Temp Tables " that added “SET NOCOUNT ON” to the stored procedure code. This seemed to fix the problem. The other poster’s problem was very similar to mine apparently…

Thanks for the help!