urgent please

19 May 2006 9:01 AM

amjad

Hi i have a varchar variable called @sql

@val1 varchar
@val2 varchar

set @sql=val1 + val2

the problem is when either of one has null value suppos val1 has null value
then @sql not accept val2 and put null value i dont know why its doing like
that if val1 is null then it should take val2 , sql should be null if both
val1 and val2 are nulls

any body has any idea where i am wroning thanks

19 May 2006 9:09 AM

Guus Kramer

Hi,

use an ISNULL nested in a CASE

See BOL for further details

G Kramer
The Netherlands

Show quote

"amjad" wrote:

> Hi i have a varchar variable called @sql
>
> @val1 varchar
> @val2 varchar
>
> set @sql=val1 + val2
>
> the problem is when either of one has null value suppos val1 has null value
> then @sql not accept val2 and put null value i dont know why its doing like
> that if val1 is null then it should take val2 , sql should be null if both
> val1 and val2 are nulls
>
> any body has any idea where i am wroning thanks
>

19 May 2006 9:11 AM

Tony Rogerson

NULL + 'value' always equals NULL unless you change SET
CONCAT_NULL_YIELDS_NULL setting...

From BOL...

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';
GO
-- SET CONCAT_NULL_YIELDS_NULL ON and testing.
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT 'abc' + NULL ;
GO

-- SET CONCAT_NULL_YIELDS_NULL OFF and testing.
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'abc' + NULL;
GO

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Show quote

"amjad" <am***@discussions.microsoft.com> wrote in message
news:CBCB856F-A7DE-4D48-83FE-E6ED7FF866CD@microsoft.com...
> Hi i have a varchar variable called @sql
>
> @val1 varchar
> @val2 varchar
>
> set @sql=val1 + val2
>
> the problem is when either of one has null value suppos val1 has null
> value
> then @sql not accept val2 and put null value i dont know why its doing
> like
> that if val1 is null then it should take val2 , sql should be null if both
> val1 and val2 are nulls
>
> any body has any idea where i am wroning thanks
>

19 May 2006 9:22 AM

Omnibuzz

either use
set @sql=isnull(@val1,'') + isnull(val2,'')
or

SET CONCAT_NULL_YIELDS_NULL ON
GO
set @sql=val1 + val2

hope this helps.

19 May 2006 9:35 AM

Vadivel

Amjad,

You need to use isNull().

Syntax for it is: isNull(your_expression, value to replace with).

Best Regards
Vadivel

http://vadivel.blogspot.com

Show quote

"amjad" wrote:

> Hi i have a varchar variable called @sql
>
> @val1 varchar
> @val2 varchar
>
> set @sql=val1 + val2
>
> the problem is when either of one has null value suppos val1 has null value
> then @sql not accept val2 and put null value i dont know why its doing like
> that if val1 is null then it should take val2 , sql should be null if both
> val1 and val2 are nulls
>
> any body has any idea where i am wroning thanks
>

19 May 2006 4:33 PM

Jim Underwood

understand that NULL is not equal to nothing. is not equal to '', and is not
equal to 0.

Null is unknown, meaning it could be anything and everything. Because of
this, any value concatenated to a null (unknown) value, will result in an
unknown, or null value.

Math with nulls produce the same results (always null)

5 + UnknownValue = UnknownValue
5 / UnknownValue = UnknownValue
5 * UnknownValue = UnknownValue

Show quote

"amjad" <am***@discussions.microsoft.com> wrote in message
news:CBCB856F-A7DE-4D48-83FE-E6ED7FF866CD@microsoft.com...
> Hi i have a varchar variable called @sql
>
> @val1 varchar
> @val2 varchar
>
> set @sql=val1 + val2
>
> the problem is when either of one has null value suppos val1 has null
value
> then @sql not accept val2 and put null value i dont know why its doing
like
> that if val1 is null then it should take val2 , sql should be null if both
> val1 and val2 are nulls
>
> any body has any idea where i am wroning thanks
>