Toad World® Forums

Defining Variables

Hi

I’m new to Toad and fairly new to SQL, I’d like to define my Variables at the top of my code, I’ve used Define, but I’m still getting a popup asking for my Variables again ?, additionally do I need to Undefine them ?, how long will Toad hold variables in Memory ?, is there a way to add a Watch to a variable in Toad, so that I can see what has been assigned ?, thanks for any help…

DEFINE StartDate = 20150101, EndDate = 20150131;
SELECT *
FROM DAILY_SALES
WHERE PROCESSED_DATE_KEY BETWEEN &StartDate AND &EndDate;

Thanks

Gavin

Hi Gavin,

to define a variable in TOAD SS you can write :

SELECT *

FROM TABLE_NAME

WHERE FIELD_NAME BETWEEN :StartDate AND :EndDate;

where TABLE_NAME was a valid name of your table , and FIELD_NAME is a valid name of the field of your table.

In your case :

SELECT *

FROM DAILY_SALES

WHERE PROCESSED_DATE_KEY BETWEEN :StartDate AND :EndDate;

At this point if you execute your statement TOAD prompt to you a dialog, where you can insert value for the variable - StartDate and EndDate.

But if you want to define variable in your T-SQL code you must use this syntax :

declare @StartDate datetime;

declare @EndDate datetime;

set @StartDate = ‘20160101’; – begin of January 2016

set @EndDate = ‘20160131’; – end of January 2016

SELECT *

FROM DAILY_SALES

WHERE PROCESSED_DATE_KEY BETWEEN @StartDate AND @EndDate;

I hope this can help you.

Best regards,

Sergio