CODEWITHSUNDEEP
sqlsql-server
flash
flash

Reputation: 1519

Using declare variable in SQL Server

I saw below question and I was not able to figure out what it will do as I am confuse on the concept of DECLARE here.

Assuming today's date is December 31st 2014 and the value of check_out_date is "March 12, 2017" when the value of currency_x_id is "119". Which of the following identify the value of @currency_date after executing the SQL statement below:

DECLARE @currency_date SMALLDATETIME = GETDATE()

SELECT @currency_date = check_out_date
FROM currency_history
WHERE currency_x_id = 119

Upvotes: 1

Views: 256

Answers (1)

Jesús López
Jesús López

Reputation: 9251

When you declare a variable you can assign an initial value to it in the same statement:

DECLARE @currency_date smalldatetime = getdate()

Later, you can assign other values to that variable. It is a variable, it means its value may change as the program executes.

The code you posted does the following:

Allocate memory for storing @currency_date
Assign current date and time (now) to @currency_date
For each row in currency_history table whose currency_x_id is equals to 119:
    assign the value of check_out_date column to @currency_date variable

So what value will contain @currency_date? Well, it depends.

If there are no rows in currency_history table with currency_x_id equals to 119, the value will be the current date and time.

If there is one row, the value will be the value of check_out_date column of that row.

If there are more rows, the value will be the last assigned value. But, which is the last one? Well, the query doesn't specify any order, SQL Server may scan the table in any order it "wants", the order is undefined, and the value assigned is not deterministic.

Upvotes: 2

Related Questions