set and reuse variables locally in sqlpage

[f8dca]

Would it be possible to add support to declaring local SQL variables and setting their value ?
something along these lines:

declare @max_inv varchar(10) ;
set  @max_inv = func_getMaxInv(); --this is a scalar function to get the latest invoice number

SELECT 'table' as component

select [id] , [invoice_no], [invoice_date], [cust_no] 
from SALES_HISTORY 
where invoice_no = isnull($invoice_no, @max_inv)

This snippet should either show the invoice passed in the parameter or the last one.

[lovasoa]

That would indeed be nice ! But this will require support from sqlparser-rs first. Let's ask them !

[lovasoa]

In your particular example, is there a reason you cannot use

select [id] , [invoice_no], [invoice_date], [cust_no] 
from SALES_HISTORY 
where invoice_no = isnull($invoice_no, func_getMaxInv())

?

[f8dca]

Yes, I can do that as workaround in this particular case. In amore generic way I still would love to be able to use local variables so there is no need to call a function many times.

[lovasoa]

Let's keep that open to keep track of the feature. It's something I'd like to see in SQLPage too

[lovasoa]

SET is supported as of 0.12.0-beta, and implemented on sqlpage's side (the variable are stored in sqlpage, not in the database).

This allows for new interesting use cases, using variable values as inputs to sqlpage functions.