declaring variable in sql using a database table selector node


I’m using a database table selector node and would like to use the following query in it

declare @timestamp datetime;
set @timestamp = ‘$${STimestamp}$$’;

select *
from XXX.dbo.IDs where timestamp = @timestamp

This fails with error message

ERROR Database Table Selector 0:201:0:209 Execute failed: Error while validating SQL query: Must declare the scalar variable “@timestamp”.

The documentation says it is possible to use set etc statements before the select if the statements are completed with a “;”. However, I am getting a problem with the declaration. I’ve tried this with various combinations of having or not having the “;”, but with no success.

I’m using version 3.4.1.

Is this something that’s allowed to do, or am I getting the syntax wrong?


Hi there!

It should be possible. You can use Database SQL Executor node before Database Table Selector node to see if it works.

Regardless of this issue I highly recommend new Knime versions :wink:


it doesn’t work in the executor node.

the statements work in sql from a SQL Server Management Studio, so this is KNIME processing the different lines of statements

I think the “;” to separate the statements is creating them into separate queries so that the declaration of the variable is not carried through to the rest of the statements. The error is about the variable not being declared when the variable is used.

I’ve tried this with other statements, it seems very temperamental about what’s acceptable and what’s not. The documentation does not state what is done to the separate statements, but just to put a semi-colon between them.

Is it possible to see what the query is that’s passed to the server before it is executed?


This trail may be helpful for you:

1 Like

thanks, the trail is a bit different.

I’ve managed to do what I need by creating #temp tables and inserting into that. It seems there is something about the scope of the declare and set statements and the scope of the variable that makes it not work.