Salesforce Marketing Cloud queries do not allow variables or temporary tables according to the "SQL Support" section of this official documentation (http://help.marketingcloud.com/en/documentation/exacttarget/interactions/activities/query_activity/)
I have a data extension called Parameters_DE with fields Name and Value that stores constant values. I need to refer to this DE in queries.
Using transact-SQL, an example is:
Declare @number INT
SET @number = (SELECT Value FROM Parameters_DE WHERE Name='LIMIT')
SELECT * FROM Items_DE
WHERE Price < @number
How can the above be done without variables or temporary tables so that I can refer to the value of the 'LIMIT' variable that is stored in Parameters_DE and so that the query will work in Marketing Cloud?
This is what I would have done anyway, even if variables are allowed:
SELECT i.*
FROM Items_DE i
INNER JOIN Parameters_DE p ON p.Name = 'LIMIT'
WHERE i.Price < p.Value
Wanting to a use a variable is indicative of still thinking procedural, instead of set-based. Note that, if you need to, you can join to the Parameters_DE table more than once (give a difference alias each time) to use the values of different parameters at different parts in a query.
You can also make things more efficient for this type of query by having a parameters table with one row, and a column for each value you need. Then you can JOIN to the table one time with a 1=1 condition and look at just the columns you need. Of course, this idea has limitations, too.