# Named Query: Error - A result set was generated for update

**URL:** <https://forum.inductiveautomation.com/t/named-query-error-a-result-set-was-generated-for-update/60782>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [June 15, 2022, 7:36am UTC](https://forum.inductiveautomation.com/t/named-query-error-a-result-set-was-generated-for-update/60782 "2022-06-15T07:36:05Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Richita\_Pandey](https://avatars.discourse-cdn.com/v4/letter/r/6bbea6/32.png) [@Richita\_Pandey](https://forum.inductiveautomation.com/u/Richita_Pandey)\
**Post date:** [June 15, 2022, 7:36am UTC](https://forum.inductiveautomation.com/t/named-query-error-a-result-set-was-generated-for-update/60782/1 "2022-06-15T07:36:05Z")

</div>

Not able to return/insert value in database using dynamic named query .  
Error - A result set was generated for update.  
Working fine in MSSQL server

```auto
DECLARE @Sql AS NVARCHAR(MAX),
@Sql1 AS NVARCHAR(MAX),
@ParmDefinition AS NVARCHAR(MAX),
@cnt INT,
@Fld varchar(max)='serial_number',
@var int,
@Fld1 varchar(max)='wi_id' ,
@Fld2 varchar(max)='equipment_id' ,
@LookupValue NVARCHAR(MAX)= :serial_number ,
@LookupValue1 NVARCHAR(MAX)=convert(nvarchar(max), :wi_id ),
@LookupValue2 NVARCHAR(MAX)=convert(nvarchar(max), :equipment_id ),
@LookupValue3 NVARCHAR(MAX)= :start_time ,
@LookupValue4 NVARCHAR(MAX)= :end_time ,
@LookupValue5 NVARCHAR(MAX)=convert(nvarchar(max), :actual_ct ),
@LookupValue6 NVARCHAR(MAX)=convert(nvarchar(max), :overshoot ),
@LookupValue7 NVARCHAR(MAX)= :CPIL ,
@LookupValue8 NVARCHAR(MAX)= :Operator ,
@LookupValue9 NVARCHAR(MAX)=convert(nvarchar(max), :target_ct ),
@Tbl varchar(max);
if @LookupValue2 >200 and @LookupValue2<250
begin
	set @Tbl='Table1'
END 
if @LookupValue2 >400 and @LookupValue2<450
begin
	set @Tbl='Table2'
END 
 set nocount on; 

SET @Sql = N'SELECT @cnt = COUNT(*) FROM ' + @Tbl +
' WHERE ' + @Fld + ' = ''' + @LookupValue + ''' and ' + @Fld1 + ' = ''' + @LookupValue1 + ''' and ' + @Fld2 + ' = ''' + @LookupValue2 + '''';

SET @ParmDefinition=N'@cnt int OUTPUT';

set nocount on;
EXEC sp_executesql @Sql,@ParmDefinition,@cnt = @var OUTPUT;
select @var as VarCount

If @var=0
Begin
SET @Sql1 = N' INSERT INTO ' + @Tbl + ' VALUES ('''+@LookupValue+''','''+@LookupValue1+''','''+@LookupValue2+''','''+@LookupValue3+''','''+@LookupValue4+''','''+@LookupValue5+''','''+
@LookupValue6+''','''+@LookupValue7+''','''+@LookupValue8+''','''+@LookupValue9+''')'
EXEC sp_executesql @Sql1
end

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 15, 2022, 12:01pm UTC](https://forum.inductiveautomation.com/t/named-query-error-a-result-set-was-generated-for-update/60782/2 "2022-06-15T12:01:24Z")

</div>

I don’t think the JDBC driver can handle this kind of query. Most likely, you’re going to have to make this a stored procedure, then call it from Ignition.

[system.db.createSProcCall - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC81/system.db.createSProcCall)

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 15, 2022, 12:24pm UTC](https://forum.inductiveautomation.com/t/named-query-error-a-result-set-was-generated-for-update/60782/3 "2022-06-15T12:24:06Z")

</div>

I would also say that making a generic query like you have there to insert into multiple tables is not really a good idea for a named query anyways.

You should just make two named queries an INSERT statement for `table1` and an INSERT statement for `table2` and in your jython you could do something like

```auto
params = {'lookupValue1':1, 'lookupValue2':225, ...}
if params['lookupValue2'] >200 and params['lookupValue2']<250:
    namedQuery = 'insertTable1'
elif params['lookupValue2'] > 400 and params['lookupValue2']<450:
    namedQuery = 'insertTable2'
system.db.runNamedQuery(namedQuery, params)

```

so you use jython to figure out what named query to run.

Trouble shooting dynamically made sql when something goes wrong is a real headache and should be avoided when possible imo.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 15, 2022, 12:29pm UTC](https://forum.inductiveautomation.com/t/named-query-error-a-result-set-was-generated-for-update/60782/4 "2022-06-15T12:29:32Z")

</div>

> [@Richita\_Pandey](#):
>
> using dynamic named query

That isn't a query. That is a SQL script. JDBC doesn't support scripts. Use single statements from the Ignition side.

{ Some DBs, like MS SQL Server, support some aspects of SQL scripts in their JDBC drivers, but it is prone to errors when used in a generic JDBC framework like Ignition. }

SQL Server Management Studio (and other SQL management tools) support scripts. Just because something runs in SSMS doesn't mean it must work in JDBC.