Using SQL Local Variables in Pre/Post Queries

[kevinwatt]

Hello!

I've created a process intended to automate the normalize of HTML tables. One of the requirements of the process is to store metadata about the time of completion and the number of errors encountered each run.

I am trying to use 'Post Create SQL Statements' to declare a local variable, set it equal to the most recent runtime ID, the use an update where statement to add the date-time to the appropriate column.

The query works in SSMS, so the error has to do something to do with how Alteryx is processing the query. I've included an example of the query below. Thank you all in advance for your help!

DECLARE @current_id int;
SELECT TOP 1 @current_id = id FROM metadata_table ORDER BY id DESC;
UPDATE metadata_table

SET end_date = GETDATE()

WHERE id = @current_id;

[RolandSchubert · Accepted solution]

Hi @kevinwatt ,

try removing the semicolon at the end of the lines:

DECLARE @current_id int
SELECT TOP 1 @current_id = id FROM metadata_table ORDER BY id DESC
UPDATE metadata_table
SET end_date = GETDATE()
WHERE id = @current_id

Sounds weird, but should work ...

Best

Roland

[kevinwatt]

It's not weird if it works. Thanks so much for the help, Roland!