# Named Query in Script, with Returned Values

**URL:** <https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [June 9, 2023, 5:45pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852 "2023-06-09T17:45:19Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [June 9, 2023, 5:45pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/1 "2023-06-09T17:45:19Z")

</div>

Server: SQL Server 2019

In this edition, I am inserting a new record into a table using a named query and a stored procedure. The SP returns two values, the record ID and another Integer.

I would like to be able to retrieve both values and use them in the rest of the process. The record ID will be used as the user adds additional information into associated tables.

This is the code I have so far:

```auto
params = {"p_Category":category, "p_Classification":classification, "p_Description":desc, "p_ShortDesc":shortDesc, "p_Stock":stocked, "p_Review":review}
returnNQ = system.db.runNamedQuery(namedQuery, params)
self.parent.parent.getChild("flx_InputArea").getChild("TabContainer").getChild("flx_General").getChild("coord_Left").getChild("TextArea_0").props.text = returnNQ

```

This is what is returned from `returnNQ`

```auto
[{"":4021}]

```

When I query the table, the other value has been created, and when I test the named query, i get both values back. 103854 and 4021.

Just to show the named query results from another insert:

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

Thoughts?

Edit:  
this is the named query:

```auto
EXEC inv.usp_Item_NewInsert :p_Category, :p_Classification, :p_Description, :p_ShortDesc, :p_Stock, :p_Review

```

Edit 2:  
This is the SP:

```auto
CREATE PROCEDURE [inv].[usp_Item_NewInsert] 
	-- Add the parameters for the stored procedure here
	@Category INT,
	@Classification INT,
	@Description VARCHAR(255),
	@ShortDesc VARCHAR(66),
	@Stocked BIT = 0,
	@Review BIT = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	DECLARE @Catalog INT
	DECLARE @ItemId INT

	SET NOCOUNT ON;

	EXEC inv.usp_GenerateCatalogNumber01 @Cat = @Catalog OUTPUT

    -- Insert statements for procedure here
	BEGIN TRANSACTION
		INSERT INTO inv.Items (CatalogNumber, [Description], CategoryID, ClassificationID, ReviewThisPart, Stocked)
		VALUES (@Catalog, @Description, @Category, @Classification, @Review, @Stocked);
	COMMIT TRANSACTION

	BEGIN
		SET @ItemId = (SELECT ItemID
		FROM inv.Items
		WHERE CatalogNumber = @Catalog)
	END

	SELECT @Catalog, @ItemId
END
GO

```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [June 9, 2023, 5:47pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/2 "2023-06-09T17:47:42Z")

</div>

How is your names query constructed?

---

<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 9, 2023, 5:48pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/3 "2023-06-09T17:48:23Z")

</div>

Sounds like you've got it setup as a scalar query.

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [June 9, 2023, 5:49pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/4 "2023-06-09T17:49:06Z")

</div>

Standard query type

---

<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 9, 2023, 5:49pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/5 "2023-06-09T17:49:41Z")

</div>

Yeah, I should've known that from your result screenshot...

---

<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 9, 2023, 5:52pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/6 "2023-06-09T17:52:31Z")

</div>

How did you determine the output of the query? Did you look at the property after the assignment? Try printing `returnNQ` from the script

```auto
system.perspective.print(returnNQ)

```

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [June 9, 2023, 6:01pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/7 "2023-06-09T18:01:53Z")

</div>

I set the text of a text area to the `returnNQ` variable.

in the output console I get, nothing meaningful

```auto
com.inductiveautomation.ignition.gateway.datasource.BasicStreamingDataset@36275b6e

```

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [June 9, 2023, 6:22pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/9 "2023-06-09T18:22:24Z")

</div>

So I updated the SP to return column names for the returned values, then I used `jsonEncode` on `returnNQ`.

Now, I have this:

```auto
{"columns":[{"name":"CatalogNum","type":"java.lang.Integer"},{"name":"ItemID","type":"java.lang.Integer"}],"rows":[[103866,4033]]}

```

So the question is: How do I retrieve the two values, 103866 and 4033? ( I feel like I've done this once before, but I can't recall where! ☹)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [June 9, 2023, 6:33pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/10 "2023-06-09T18:33:46Z")

</div>

Using jsonEncode or setting the named query to return JSON you should be able to use this:

```auto
returnNQ['rows'][0][0]

```

Not using the `jsonEncode` and not modifying the named query you should be able to use this:

```auto
returnNQ.getValueAt(0,'CatalogNum')

```

or

```auto
returnNQ = system.dataset.toPyDataSet(system.db.runNamedQuery(namedQuery,params))
system.perspective.print(returnNQ[0]['CatalogNum'])

```

---

<div class="post-metadata">

**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\
**Post date:** [June 9, 2023, 8:23pm UTC](https://forum.inductiveautomation.com/t/named-query-in-script-with-returned-values/74852/11 "2023-06-09T20:23:25Z")

</div>

> [@lrose](#):
>
> Using jsonEncode or setting the named query to return JSON you should be able to use this:
> 
> `returnNQ['rows'][0][0]`

To do this, I need to use `jsonGet`. And the rest of your examples work fine as well.

Thank you!