Hi
I am trying to assign the value of the variable using the output of a query but I am getting an error. How do I fix it?
Query:
DECLARE @LATEST_DATE DATE;
SET @LATEST_DATE = SELECT MAX(MY_DATE) FROM DBO.TABLE
Error:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'SELECT'.
Thank you
Either below; the first is more typical:
DECLARE @LATEST_DATE DATE;
SELECT @LATEST_DATE = MAX(MY_DATE) FROM DBO.TABLE
--or:
SET @LATEST_DATE = (SELECT MAX(MY_DATE) FROM DBO.TABLE);
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 27, 2024 at 5:38 am
Either below; the first is more typical:
DECLARE @LATEST_DATE DATE;
SELECT @LATEST_DATE = MAX(MY_DATE) FROM DBO.TABLE
--or:
SET @LATEST_DATE = (SELECT MAX(MY_DATE) FROM DBO.TABLE);
I generally use the 2nd version of the statement if I want to cater for a possible null return
DECLARE @LATEST_DATE DATE;
SET @LATEST_DATE = ISNULL((SELECT MAX(MY_DATE) FROM DBO.TABLE), '2024-12-25');
December 27, 2024 at 3:06 pm
You can check for NULL as well in either format:
DECLARE @LATEST_DATE date;
SELECT @LATEST_DATE = ISNULL(MAX(MY_DATE), ...) FROM DBO.TABLE
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 30, 2024 at 12:23 pm
Correct. However (IT DEPENDS), there are times when a variable will not be updated using ISNULL. That is why it is sometimes necessary to use the other syntax. In the previous example, the MAX aggregate function returns a 0, so it always appears to work.
DECLARE @Name varchar(20), @Score int;
DECLARE @Data TABLE (
Name varchar(20) NOT NULL PRIMARY KEY
, Score int NULL
);
INSERT INTO @Data ( Name, Score )
VALUES ( 'John' , 10 )
, ( 'Mary' , NULL );
/*****************************************
*** Name exists, and valus is not null
*****************************************/SELECT @Name = 'John', @Score = NULL;
SELECT @Score = ISNULL(d.SCore, 0)
FROM @Data AS d
WHERE d.Name = @Name;
SELECT Name = @Name, Score = @Score, Note = 'Correct Score';
/*****************************************
*** Name exists, and valus is null.
*** ISNULL will take care of the default
*****************************************/SELECT @Name = 'Mary', @Score = NULL;
SELECT @Score = ISNULL(d.SCore, 0)
FROM @Data AS d
WHERE d.Name = @Name;
SELECT Name = @Name, Score = @Score, Note = 'Default Score';
/*****************************************
*** Name does not exists. ISNULL will not work, as there
*** is no matching record, so the variable will not be updated.
*****************************************/SELECT @Name = 'Scooter', @Score = NULL;
SELECT @Score = ISNULL(d.SCore, 0)
FROM @Data AS d
WHERE d.Name = @Name;
SELECT Name = @Name, Score = @Score, Note = 'Default Score not applied';
/*****************************************
*** Name does not exists. Use this syntax
*** in order for ISNULL to work.
*****************************************/SELECT @Name = 'Scooter', @Score = NULL;
SET @Score = ISNULL((SELECT d.SCore
FROM @Data AS d
WHERE d.Name = @Name)
, 0);
SELECT Name = @Name, Score = @Score, Note = 'Default Score correct';
December 30, 2024 at 5:52 pm
You should also be aware that the SELECT version will return the 'last' row from the result - however that last row is determined - whereas the SET version will fail if more than 1 row is returned.
As for NULL values - if the query doesn't return any data (empty set) then the variable assignment is not done using the SELECT version, which is why using the SET version and wrapping the query using ISNULL or COALESCE works. The ISNULL/COALESCE handles either situation - an empty set or a NULL value.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy