SQL Stored Procedure Call Using List of Parameters

I have a number of screens that display and allow modification of table data in MSSQL (2014).
The records represent containers in a production environment and are used to track and validate the materials being produced and added into various processes.
One of the functions on the screens is to “empty” a container.
A request was made to allow for multiple record selections in the Power Table and essentially perform a “batch empty”.
It was a simple change, but if there are a lot of records selected (1000s) then the looping script drags and affects the performance of the client.
I was hoping to find a way to pass a list of the container IDs to a stored procedure that would then “empty” the containers on the SQL Server side, freeing up resources on the limited client machine.
One possible solution I’ve found in my searching is the use of Table-Valued Parameters.

  1. Is there a way to use these, without having to purchase/install third party libraries, in Ignition?
  2. Are there other ways to pass a list to a stored procedure and have SQL do a “mass update”?

Thanks!

Via stored procedure, no. Via IN clause in the WHERE clause on an UPDATE or DELETE statement, yes.

This is intriguing, Could you provide a little more detail? Maybe an example?

Sure, here is more information.
I have a table in SQL Server that represents real world containers.
Here is a screen shot of that table as displayed in a Power Table on an Ignition window.


There is currently functionality to allow a supervisor to select multiple rows and “empty” the containers. This is currently accomplished by getting the selected rows, looping through that subset of the table data and gets the ContainerID of the each selected row. a system.db.runPrepUpdate instruction is then configured and executed (as shown here).

prepUpdate = "UPDATE tblTnTSFGTote SET Material = ‘’, MaterialDescription = ‘’, Plant = ‘’, OrderNum = ‘’, EqFilled = ‘’, EqEmptied = ‘HMI’, InProcess = 0, Empty = 1, EmptyDate = GETDATE() WHERE ContainerID = ?"
result = system.db.runPrepUpdate(prepUpdate, [containerID], ‘TnT’)

The problem is that if they select several hundred containers this routine really boggs down. It can make the client unresponsive. It can take a quite some time to complete.

What I was hoping to be able to do instead was to build some kind of list/directory/etc. and pass it to a stored procedure as a parameter and then use something like a “WHERE ContainerID IN [my list]” and allow SQL to do the work. I would still have to loop through the selected rows to build the list, but I don’t think that would be as intensive as executing the updates one at a time.

To define “empty”, basically I simply clear most of the fields in the records, or set a few status bits to 1/0 as appropriate for an “empty” container.

Does that provide the detail requested?

Thanks.

I thought about something like this last night. I haven’t tried it yet though.
In my runPrepUpdate query, could I simply have something like:

“UPDATE tblTnTSFGTote SET Material = ‘’, MaterialDescription = ‘’, Plant = ‘’, OrderNum = ‘’, EqFilled = ‘’, EqEmptied = ‘HMI’, InProcess = 0, Empty = 1, EmptyDate = GETDATE() WHERE ContainerID IN ?”

Then have the “?” parameter be a list of container IDs compiled from the selected rows.
Is this what you were referring to?

Thanks.

I tried this but I’m getting the error
SQLServerException: The index 2 is out of range
I think this is because there is only the one placeholder ("?") in my query and there are multiple elements in the list.

Ideas?

MikeAllgood,

This is very helpful. I need to do something like this. Like you, I wish I could use a User-Defined-Table-Type because it is so powerful and I’m used to writing stored procedures this way. But, this might be better than nothing. I’ll play around with it. Thanks.

Doug

Right. You have to dynamically put in the number of question marks to match the number of elements in the list. Note that this technique cannot be turned into a named query (yet?).

If you are comfortable with the security implications you can always build the IN clause and pass it.


q = "UPDATE Table SET Field='' WHERE IDField IN "
t = system.dataset.toPyDataSet(event.source.parent.getComponent('Power Table').data)
c = '('
for row in t:
	if row[3]==1:  #Check the Boolean field for True
		c+=str(row[0])+','
c=c[:-1]		
c+=')'
q = q + c
result = system.db.runUpdateQuery(q,'DataBaseConnection')
print result