Prev: jameelmossly@yahoo.com
Next: "Cannot generate SSPI context."
From: Roy Goldhammer on 25 Jul 2010 08:27
Hello there

I'm running this code:

declare @ProductType int

set @ProductType = 0;

select @ProductType = ProductType
from dbo.Products
where ProductID = 6;

If there isn't any productID 6 @ProductType remains 0

However if there is ProductID 6 and ProductType is null @ProductType becomes
null.

Is there a way to prevent it automaticly witout set isnull() on any object?


From: Jeroen Mostert on 25 Jul 2010 08:50
On 2010-07-25 14:27, Roy Goldhammer wrote:
> I'm running this code:
>
> declare @ProductType int
>
> set @ProductType = 0;
>
> select @ProductType = ProductType
> from dbo.Products
> where ProductID = 6;
>
> If there isn't any productID 6 @ProductType remains 0
>
> However if there is ProductID 6 and ProductType is null @ProductType becomes
> null.
>
> Is there a way to prevent it automaticly witout set isnull() on any object?
>
>
I have no idea what exactly you're asking for. The behavior you mention is
what should happen, in any case. If you don't want @ProductType to be NULL
after the SELECT, just set it back to 0 if it's NULL. If you don't want any
row in Products to have a NULL ProductType, run an UPDATE to give those
records values (or a DELETE if the rows themselves are not desired), then
use ALTER TABLE ALTER COLUMN to make ProductType NOT NULL.

--
J.
From: Sylvain Lafontaine on 25 Jul 2010 11:00
Not sure to understand the question but you can try to add the condition
"ProductType is Not Null":

select @ProductType = ProductType
from dbo.Products
where ProductID = 6 AND ProductType is Not Null;


--
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Roy Goldhammer" <royg(a)yahoo.com> wrote in message
news:%23ml0HT$KLHA.1868(a)TK2MSFTNGP05.phx.gbl...
> Hello there
>
> I'm running this code:
>
> declare @ProductType int
>
> set @ProductType = 0;
>
> select @ProductType = ProductType
> from dbo.Products
> where ProductID = 6;
>
> If there isn't any productID 6 @ProductType remains 0
>
> However if there is ProductID 6 and ProductType is null @ProductType
> becomes null.
>
> Is there a way to prevent it automaticly witout set isnull() on any
> object?
>


From: --CELKO-- on 25 Jul 2010 19:15
>> if there is product_id = 6 and product_type is NULL @in_product_type becomes NULL <<

Of course.

>> Is there a way to prevent it automatically without set COALESCE() on any object? <<

If a NULL product_type makes no sense in your data model, use DDL to
forbid it. Why did yo let zero and NULL have the same meaning in the
data anyway?


 | 
Pages: 1
Prev: jameelmossly@yahoo.com
Next: "Cannot generate SSPI context."