Null Values... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Null Values...

Author

Topic

masterx81
Starting Member

12 Posts

Posted - 2003-09-22 : 09:10:47

Hi to all...
I have again a problem...
The query is:
select * from 'table'
The problem is:
I need to return 0 instead of the null value
The same query need to work in different table with different structures...
Thanks to all!!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 09:15:11

Can you give an example? in what context are you executing the "SELECT ..." statement? (for example, in Query analyzer only, on a web page, using Access, Crystal reports, Excel, etc).

- Jeff

masterx81
Starting Member

12 Posts

Posted - 2003-09-22 : 09:22:15

Very sorry :p
I call the query via Visual Basic .net.
If the values contained in one returned field is null, it return '0' instead of the null value...
Thanks for the answer :p

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 09:24:53

use the ISNULL() function.

SELECT ISNULL(SomeValue,0) as SomeValue FROM YourTable

surround each column with ISNULL() that might be null. Read about it more in books on-line.

You also can write your own function in VB.NET that takes an argument and returns a nicley formatted value for you (with commas and all that) and returns a 0 instead of Nulls.

I use something like that to display "-" on reports instead of 0 since many times it makes things more readable ...

- Jeff

masterx81
Starting Member

12 Posts

Posted - 2003-09-22 : 09:32:08

But i can't use the isnull :'(
I need to use the same query for multiple tables that have different columns...
If i use a function i need to loop to all the columns ad set the fields that are null to 0...
Is possible?

thanks for the interest...

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-22 : 09:32:27

You shouldn't code select * - better to name all the columns then the problem goes away because you can use coalesce on the column.
If you want an SP which will do this (wouldn't advise it)
Something like

create proc s_selall
@tablename varchar(128)
as

declare @sql varchar(8000)
select @sql = coalesce(@sql + ',', '') + c.COLUMN_NAME + case when c.DATA_TYPE like '%char%' then '=coalesce(' + c.COLUMN_NAME + ','''')'
when c.DATA_TYPE like '%datetime%' then '=coalesce(' + c.COLUMN_NAME + ',''19000101'')'
else '=coalesce(' + c.COLUMN_NAME + ',0)'
end
from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME = @tablename
select @sql = 'select ' + @sql + ' from ' + @tablename

exec (@sql)
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-22 : 09:38:10

quote:
Originally posted by masterx81

But i can't use the isnull :'(
I need to use the same query for multiple tables that have different columns...
If i use a function i need to loop to all the columns ad set the fields that are null to 0...
Is possible?

thanks for the interest...

If it's so highly dynamic, how do you know what you're working with.

Why are all these SELECTs using dynamic sql...why not code a sproc to return your results?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 09:38:11

Or:

use a function in VB.NET like i mentioned, then the issue is irrelevant. In fact, that is probably the best way to do this because it is just a presentation issue: you simply want to DISPLAY the value of "0" for these tables, even though they CONTAIN the value Null.

just create a function in VB.NET. I am not a .NET guy (yet!) but in ASP you would then say:

<%= somefunction( recordset("ColumnName"))%>

or

<%=somefunction(recordset(Colnumber))%>

and define SomeFunction as:


function SomeFunction(val)
  if isnull(val) then SomeFunction = 0 : exit function
  SomeFunction = Val
end function

- Jeff

masterx81
Starting Member

12 Posts

Posted - 2003-09-22 : 10:06:01

Thanks...
I wanted to leave the job at the server, but if is not simple I make it through vb with a function (thanks jsmith8858)...
Nice forum... Nice people!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-22 : 10:46:43

In .Net, you need to use Convert.IsDBNull(expression),works just like IsNull().

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources