I need to create a stored proc that accepts a list of one or more id's (example "1,12,13,98") then uses those ids' in a simple UPDATE. Obviously this sql wouldn't work, but it demonstrates the idea:
CREATE PROCEDURE MyProc ( listOfIDs VARCHAR(...) )
SET SomeFlag = 1
WHERE IDColumn IN ( listOfIDs )
I already know I can use dynamic SQL for this, but ... I'd like to avoid that if possible. Are there any other
options for MySQL?
For example, in SQL Server I could use xml functions. Just pass in the id's as an xml string the use a regular JOIN like below. Does mySQL have anything similar?
DECLARE @str XML
SET @str = '<id>1</id><id>12</id>....'
; WITH t AS (
SELECT ParamValues.id.value('.', 'INTEGER') AS ID
FROM @str.nodes('//id') ParamValues(id)
FROM other Table ot INNER JOIN t ON t.id = ot.id