0

I can't seem to figure out how to load the value returned by the following expression/subquery into a variable:

declare @var int
set @var = null

  IF @var IS NULL
SELECT @var = t.col_one
    FROM my_table t
    WHERE t_datetime = (SELECT MAX(t_datetime) FROM t WHERE t.col_two = 1)

How can I load the result of the expression into the variable?

I've updated the code to reflect answers below however the issue has persisted. There are no errors but later in my sproc when I call @var the variable is still null. Which means this still isn't working. Later in the code I'm using:

t.col_three = @var

Further I'm not using t.col_three = @var or @var is null because the variable cannot be null:

Improve this question
6
  • Are you getting an error? If so what? That should work as long as the subquery returns exactly one row and the variable is a compatible datatype. Commented Nov 16, 2012 at 21:35
  • No error. @var simply isn't loading/populating the result. Commented Nov 16, 2012 at 21:54
  • Does the query return any value though? Commented Nov 16, 2012 at 22:14
  • I wish it did... no it doesn't. Because I'm not using t.col_three = @var or @var is null nothing is returned. When I do use that, NULL is returned. Commented Nov 16, 2012 at 22:18
  • So, are you saying that the query returns no results when ran without the assignment to the variable? It would make sense for the variable to remain NULL if that's the case... Commented Nov 16, 2012 at 22:21

3 Answers 3

Reset to default
3

Try changing the query to the below. I will achieve what you need without the use of sub queries and it will return always a maximum of 1 result unlike your query.

Select Top 1 @var = t.col_one
From my_table t
Where t.col_two = 1
Order By t.t_datetime Desc
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Try 

SELECT @var = table.col from table ...

Also make sure the query only returns one row.

For example, add something like this right before your query:

DECLARE @count INT
SELECT @count = COUNT(*) FROM t WHERE t.col_two = 1
IF (@count = 0) RAISERROR('no rows found',16,1)

and see if it throws the error.

Improve this answer

2 Comments

Could you please finish the code "..." I've used select (see updated code in my initial post) I'm still unable to get the variable to select or set the value returned by the expression.
Actually I like the solution suggested by A. Agius better, using top 1 and order by desc. If it still doesn't work, do some debugging to figure out if there are any rows with t.col_two = 1. See edit above. btw. depending on the size of my_table, you probably should consider creating indexes on the t_datetime and col_2 columns.
0

As long as your query returns only one row, either SET or SELECT will work fine. You can use aggregate functions such as MAX in order to ensure you only have one row, or you can SELECT TOP 1 to ensure you only return one row. If you do use TOP 1, though, take care to also ORDER your query in a way that gives you the row you want.

When setting a variable this way, I prefer to use TOP 1 only as a safeguard -- an operation like this is really looking for a discrete value, and your query should be written in that way, too.

Improve this answer

4 Comments

Totally agree with @D. Lambert Because with your query there is the possibility that multiple records are returned.
Multiple records returned in an assignment statement only causes the variable to be assigned multiple times, which wouldn't result in the variable being null unless the very last record in the result set were null.
I guess the point is that you want to be sure you're in control of which row is picked, using ORDER BY to sort the row you want to the top.
@ComputerGuy - in the query you posted, the MAX is inside your subquery, so there's nothing to prevent multiple rows in the actual SELECT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.