May 17, 2012 at 10:11 am
What is best way to write this statement?
1)
DECLARE @L_ZERO INT
SELECT @L_ZERO = IdMyTable FROM MyTable WHERE ColumnRef = 1000
SELECT ISNULL(@L_ZERO, 0)
OR
2)
DECLARE @L_ZERO INT
SET @L_ZERO = ISNULL((SELECT IdMyTable FROM MyTable WHERE ColumnRef = 1000), 0)
SELECT @L_ZERO
Thanks,
May 17, 2012 at 10:20 am
How's this?
DECLARE @L_ZERO INT
SELECT @L_ZERO = ISNULL(IdMyTable,0) FROM MyTable WHERE ColumnRef = 1000
SELECT @L_ZERO
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
May 17, 2012 at 10:38 am
Alexeins (5/17/2012)
What is best way to write this statement?1)
DECLARE @L_ZERO INT
SELECT @L_ZERO = IdMyTable FROM MyTable WHERE ColumnRef = 1000
SELECT ISNULL(@L_ZERO, 0)
OR
2)
DECLARE @L_ZERO INT
SET @L_ZERO = ISNULL((SELECT IdMyTable FROM MyTable WHERE ColumnRef = 1000), 0)
SELECT @L_ZERO
How about this?
DECLARE @L_ZERO INT = (SELECT ISNULL(IdMyTable,0) FROM MyTable WHERE ColumnRef = 1000);
SELECT @L_ZERO;
Or, if you're worried about the whole SELECT being NULL, then:
DECLARE @L_ZERO INT =
(SELECT ISNULL((SELECT IdMyTable FROM MyTable WHERE ColumnRef = 1000),0);
SELECT @L_ZERO;
May 17, 2012 at 11:18 am
I prefer this to prevent NULL values.
DECLARE @L_ZERO INT = 0
SELECT @L_ZERO = IdMyTable FROM MyTable WHERE ColumnRef = 1000
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 17, 2012 at 11:23 am
Nakul Vachhrajani (5/17/2012)
How's this?
DECLARE @L_ZERO INT
SELECT @L_ZERO = ISNULL(IdMyTable,0) FROM MyTable WHERE ColumnRef = 1000
SELECT @L_ZERO
This doesn't produce the same results as the original queries. Specifically, when there is no record with ColumnRef = 1000, this will produce a NULL value whereas the other two queries will produce a 0 value.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 17, 2012 at 11:29 am
The only problem with this is when ColumnRef = 1000 doesn't exists, because variable still being NULL.
May 17, 2012 at 11:44 am
drew.allen (5/17/2012)
I prefer this to prevent NULL values.
DECLARE @L_ZERO INT = 0
SELECT @L_ZERO = IdMyTable FROM MyTable WHERE ColumnRef = 1000
Drew
Drew, how does that prevent NULL from overriding your 0?
May 17, 2012 at 12:12 pm
Brandie Tarvin (5/17/2012)
drew.allen (5/17/2012)
I prefer this to prevent NULL values.
DECLARE @L_ZERO INT = 0
SELECT @L_ZERO = IdMyTable FROM MyTable WHERE ColumnRef = 1000
Drew
Drew, how does that prevent NULL from overriding your 0?
I'm assuming that IdMyTable is not nullable, which may not be a valid assumption, but is certainly a reasonable assumption given the name of the field. The query will update @L_ZERO for every record in the result set. If there are no records in the result set, there are no updates, and the variable will retain its default zero value.
If IdMyTable IS nullable, then my query won't work.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 17, 2012 at 12:22 pm
Alexeins (5/17/2012)
The only problem with this is when ColumnRef = 1000 doesn't exists, because variable still being NULL.
Did you already know the answer before you posted?
You initially asked about the best way to write a SQL statement...well it depends on the expected result and your environment. Please post DDL for the tables you're referencing, some sample data and the desired results and we can better assist. Right now everyone is forced to guess at what you're after.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 21, 2012 at 2:22 am
Using SELECT over SET "may" give unexpected results. Consider the following:
CREATE TABLE #tmpstore
(
id INT ,
name VARCHAR(10)
);
---- creating a table to insert temp data
INSERT INTO #tmpstore
SELECT 1, 'Chris'
UNION ALL
SELECT 2 , 'Agile'
UNION ALL
SELECT 3 , 'Microsoft';
---- populating data into tempstore
DECLARE @int INT;
SET @int = 1;
--- looping thru records in tmpstore and reinstering them into the same table.
WHILE @int <= 5
BEGIN
DECLARE @id INT , @name VARCHAR(10);
SELECT @id = ID , @name = name FROM #tmpstore
WHERE id = @int;
INSERT INTO #tmpstore
SELECT @id , @name;
SET @int += 1;
END
SELECT * FROM #tmpstore;
GO
DROP TABLE #tmpstore;
The @ID and @Name variables will be reset when the relevant id is found in #tmpstore, but when no value is found for id 4 and 5 the original values for id 3 are held In the variables.
Now consider the following example:
CREATE TABLE #tmpstore
(
id INT ,
name VARCHAR(10)
);
---- creating a table to insert temp data
INSERT INTO #tmpstore
SELECT 1, 'Chris'
UNION ALL
SELECT 2 , 'Agile'
UNION ALL
SELECT 3 , 'Microsoft';
---- populating data into tempstore
DECLARE @int INT;
SET @int = 1;
--- looping thru records in tmpstore and reinstering them into the same table.
WHILE @int <= 5
BEGIN
DECLARE @id INT , @name VARCHAR(10);
SET @id = (SELECT id FROM #tmpstore WHERE id = @int);
SET @name = (SELECT name FROM #tmpstore WHERE id = @int);
INSERT INTO #tmpstore
SELECT @id , @name;
SET @int += 1;
END
SELECT * FROM #tmpstore;
GO
DROP TABLE #tmpstore;
When the variables are being set inside the loop, when id 4 and 5 are not found the variables are set to NULL. This will cause null entries to be inserted into #tmpstore.
Just something to be aware of 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply