Try this
SELECT TOP 1
@a = id
FROM
test
WHERE
name = 'notexist'
If @@rowcount = 0
set @a = NULL
or
set @a = case when @@rowcount = 0 then null else @a end
But its better to avoid the initialization of 1. By default it will be NULL
Try this
SELECT TOP 1
@a = id
FROM
test
WHERE
name = 'notexist'
If @@rowcount = 0
set @a = NULL
or
set @a = case when @@rowcount = 0 then null else @a end
But its better to avoid the initialization of 1. By default it will be NULL
Assign value this way
SET @a = (
SELECT TOP 1
id
FROM
test
WHERE
name = 'notexist'
)
SELECT @a
When the where clause of the select filter out every records then no assignation is made. Therefore, @x has the value of the last time it was assigned.
Try this way instead :
SELECT @X = (SELECT ContractDate
FROM dbo.Foo C
WHERE ProjectCode = 130192
AND VersionID = (SELECT MIN(VersionID)
FROM dbo.Foo
WHERE ProjectCode = C.ProjectCode))
This way, your assignation "select" doesn't contains any where clause, thus it will always be executed. If the inner select doesn't result anything, then null is assigned to X.
Here is another example
DECLARE @x NVARCHAR(25) = 'Initial value'
SELECT @x = 'Value1'
WHERE 0 = 1
--@x is still "Initial value" cause 0 isn't egal to 1
SELECT @x = (SELECT 'Value2'
WHERE 0 = 1)
--@x is now null because the impossible where clause didn't hindered the assignation. It has just made the inner select to return null instead.
As Martin suggestion, you can instead reset the value before with
SELECT @X = null
but I prefer to have just one assignation select because it's shorter, and it is one less instruction line for the SQL Engine.
Side note :
Your "select" could be rewritten with an "Order by" and "TOP 1" clause to get the minimum. It's give the same result and is shorter to be write/read.
SELECT @ProjectContractDate_T0 = (SELECT TOP 1 ContractDate
FROM dbo.FooFoo C
WHERE ProjectCode = @ProjectCode
ORDER BY VersionID)
It does the same thing.
When setting a variable in a select statement, if that select statement returns no rows, then there will be no assignment; your variable's value will not change.
As to your loop problem, the scope of a variable in T-SQL is the batch, not the loop. It is not re-declared or initialized with each iteration.
As such, it can be a good practice in T-SQL to explicitly initialize your variables to null when you otherwise might expect a re-initialization in a different language. This might make it more readable and better show your intent.
Also, I should add that cursors and loops in T-SQL are usually best avoided when a set-based solution (query) is possible, which is almost always. Obviously this depends on your use case, which wasn't stated.
You have a lot of options for substituting NULL values in MySQL:
CASE
select case
when fieldname is null then '123'
else fieldname end as fieldname
from tablename
COALESCE
select coalesce(fieldname, '123') as fieldname
from tablename
IFNULL
select ifnull(fieldname, '123') as fieldname
from tablename
There is a statement called IFNULL, which takes all the input values and returns the first non NULL value.
example:
select IFNULL(column, 1) FROM table;
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull
There's a couple of ways you can go about this;
Trivial, using the CASE statement;
SELECT
CASE
WHEN a = b AND a = 0 THEN a
ELSE NULL
END
FROM x
Returns:
0
NULL
NULL
NULL
Using a LEFT JOIN:
SELECT X1.a
FROM x
LEFT JOIN (SELECT a, b FROM x WHERE a = b AND a = 0) AS X1 ON x.a = X1.a AND x.b = X1.b
Returns:
0
NULL
NULL
NULL
The WHERE clause acts as a filter defining which rows are returned, but what you are asking is to have all rows returned, and apply a function on the returned rows to define the values returned for each row.
SELECT table1.val1
, table1.val2
, x.a
, table3.val3
FROM table1
JOIN table2
on table1.val1 = table2.val1
JOIN table3
on table1.val2 = table3.val3
CROSS JOIN (SELECT CASE WHEN a = b AND a = 0 THEN a END AS a
FROM letters) x
Try:
SELECT C.CustomerId, C.CustomerName, C.StatusId, I.AuthorityId
FROM Customer C
LEFT JOIN Identifier I ON I.CustomerId = C.CustomerId and I.AuthorityId = 11
WHERE C.StatusId = 1
ORDER BY C.CustomerName
just change JOIN into LEFT JOIN
SELECT C.CustomerId, C.CustomerName, C.StatusId, I.AuthorityId
FROM Customer C
LEFT JOIN Identifier I ON I.CustomerId = C.CustomerId
WHERE C.StatusId = 1
AND I.AuthorityId = 11
ORDER BY C.CustomerName
UPDATE
if you want to change (NULL) value into NULL or whatever word you want to replace, you can you COALESCE
SELECT C.CustomerId, C.CustomerName, C.StatusId, COALESCE(I.AuthorityId, 'whatever')
FROM Customer C
LEFT JOIN Identifier I ON I.CustomerId = C.CustomerId
WHERE C.StatusId = 1
AND I.AuthorityId = 11
ORDER BY C.CustomerName
Use the following:
SELECT RegName,
RegEmail,
RegPhone,
RegOrg,
RegCountry,
DateReg,
ISNULL(Website,'no website') AS WebSite
FROM RegTakePart
WHERE Reject IS NULL
or as, @Lieven noted:
SELECT RegName,
RegEmail,
RegPhone,
RegOrg,
RegCountry,
DateReg,
COALESCE(Website,'no website') AS WebSite
FROM RegTakePart
WHERE Reject IS NULL
The dynamic of COALESCE is that you may define more arguments, so if the first is null then get the second, if the second is null get the third etc etc...
As noted above, the coalesce solution is preferred. As an added benefit, you can use the coalesce against a "derived" value vs. a selected value as in:
SELECT
{stuff},
COALESCE( (select count(*) from tbl where {stuff} ), 0 ) AS countofstuff
FROM
tbl
WHERE
{something}
Using "iif" or "case" you would need to repeat the inline whereas with coalesce you do not and it allows you to avoid a "null" result in that return...
Don't put NULL inside quotes in your update statement. This should work:
UPDATE table SET field = NULL WHERE something = something
You're probably quoting 'NULL'. NULL is a reserved word in MySQL, and can be inserted/updated without quotes:
INSERT INTO user (name, something_optional) VALUES ("Joe", NULL);
UPDATE user SET something_optional = NULL;