# Named Query works while testing but return error when Binding

**URL:** <https://forum.inductiveautomation.com/t/named-query-works-while-testing-but-return-error-when-binding/90519>\
**Category:** Ignition\
**Tags:** database, ignition81, perspective\
**Created:** [May 16, 2024, 8:00pm UTC](https://forum.inductiveautomation.com/t/named-query-works-while-testing-but-return-error-when-binding/90519 "2024-05-16T20:00:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Leonardo\_Lozada](https://avatars.discourse-cdn.com/v4/letter/l/35a633/32.png) [@Leonardo\_Lozada](https://forum.inductiveautomation.com/u/Leonardo_Lozada)\
**Post date:** [May 16, 2024, 8:00pm UTC](https://forum.inductiveautomation.com/t/named-query-works-while-testing-but-return-error-when-binding/90519/1 "2024-05-16T20:00:46Z")

</div>

Hello everyone, this is my first post. I am kind of new using Ignition and I am facing an issue with a Named Query that works when I do testing, but results in an "Error\_Exception" when Binding. Following the Named Query, and some screenshots of what I see:

Query:

```python
-- Parameter definition
DECLARE @StartTime NVARCHAR(MAX) = :StartTime;
DECLARE @EndTime NVARCHAR(MAX) = :EndTime;
DECLARE @TableName NVARCHAR(MAX) = {TableName};
DECLARE @columnName NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Get column names for Fault Types
DECLARE faultCursor CURSOR FOR 
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME LIKE '%Fault';

-- Build dynamic Fault Types query
SET @sql = '
SELECT
    ft1.t_stamp AS Trigger_Time,
    ft2.t_stamp AS Clear_Time,
        CASE
';

-- Append CASE for each fault type column
OPEN faultCursor;
FETCH NEXT FROM faultCursor INTO @columnName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = @sql + ' WHEN ft1.' + QUOTENAME(@columnName) + ' = 1 THEN ''' + REPLACE(@columnName, '_Fault', '') + ''' ' + CHAR(10);
    FETCH NEXT FROM faultCursor INTO @columnName;
END
CLOSE faultCursor;
DEALLOCATE faultCursor;

SET @sql = @sql + '
ELSE ''Station''
    END AS Fault_Type,
    DATEDIFF (SECOND, ft1.t_stamp, ft2.t_stamp) AS TTR
FROM
    ' + @TableName + ' ft2
INNER JOIN
        ' + @TableName + ' ft1 ON ft1.t_stamp = (
        SELECT MAX (t_stamp)
        FROM ' + @TableName + '
        WHERE Fault_Time_Enable = 1
        AND t_stamp < ft2.t_stamp)
WHERE
    ft2.Fault_Time_Enable = 0
AND
    ft1.t_stamp BETWEEN @StartTime AND @EndTime
AND
    ft2.t_stamp BETWEEN @StartTime AND @EndTime;
';

--Execute
EXEC sp_executesql @sql, N'@StartTime DATETIME, @EndTime DATETIME', @StartTime, @EndTime;

```

This is what I get when Testing:

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

However, this is what I get when Binding:

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/6/b609b1ccf8349640f0a0951477fe508ff32fe0d9.png)

Any help would be appreciated.

---

<div class="post-metadata">

**Author:** ![sebastien\_ng](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sebastien_ng/32/55297_2.png) [@sebastien\_ng](https://forum.inductiveautomation.com/u/sebastien_ng)\
**Post date:** [May 16, 2024, 8:31pm UTC](https://forum.inductiveautomation.com/t/named-query-works-while-testing-but-return-error-when-binding/90519/2 "2024-05-16T20:31:46Z")

</div>

Maybe your binding params ou expression is not correct.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 16, 2024, 8:47pm UTC](https://forum.inductiveautomation.com/t/named-query-works-while-testing-but-return-error-when-binding/90519/3 "2024-05-16T20:47:38Z")

</div>

Are you sure you are passing in `Station107Faults` and not `'Station107Faults'`?

Also, you can delete all your `@DECLARE` statements and use the `:value` and `{QueryString}` parameters directly in the named query.

---

<div class="post-metadata">

**Author:** ![Leonardo\_Lozada](https://avatars.discourse-cdn.com/v4/letter/l/35a633/32.png) [@Leonardo\_Lozada](https://forum.inductiveautomation.com/u/Leonardo_Lozada)\
**Post date:** [May 17, 2024, 4:35pm UTC](https://forum.inductiveautomation.com/t/named-query-works-while-testing-but-return-error-when-binding/90519/4 "2024-05-17T16:35:14Z")

</div>

The parameters are passed as strings just like while Testing, I did several changes to my query and now it works.

Thanks!

---

<div class="post-metadata">

**Author:** ![Leonardo\_Lozada](https://avatars.discourse-cdn.com/v4/letter/l/35a633/32.png) [@Leonardo\_Lozada](https://forum.inductiveautomation.com/u/Leonardo_Lozada)\
**Post date:** [May 17, 2024, 4:36pm UTC](https://forum.inductiveautomation.com/t/named-query-works-while-testing-but-return-error-when-binding/90519/5 "2024-05-17T16:36:10Z")

</div>

I tried both ways, and got different faults... Got rid od the {QueryString} and made it a String with some additional changes to the SQL code and now it works.

Thanks for the suggestions.