May 26, 2006 at 12:39 pm
Hi All
I suppose that this is a stupid question, but a very frustrating one for me.
In SQL 2005 Management Studio, when displaying the result of a query, as text, in the result window, how can I have Nulls's displayed as nothing, in place of "NULL".
The result is from a SP that outputs alot of tables, and in someplaces separates colunms with nulls. I need to paste this in Excel to do some calcs and don't want the nulls all over the show.
Thanks, Martin
May 26, 2006 at 12:42 pm
In your query you can put a case statement
case when is null then '' else value
May 26, 2006 at 12:47 pm
I don't know how to configure the user interface, but in your query you can use Case to replace NULL values with an empty string as two single quotes using maybe ISNULL function. I would be very careful what I replace with what because Excel determines the Data Type of the column by first 8 rows and I had import /export issues when I had empty cells in Excel in first 8 rows. I had explicitly put something in the cells like 0 for integer column or a blank for string column
Regards,Yelena Varsha
May 26, 2006 at 3:04 pm
Thanks for your replies, but the problem is that the results are from a very complicated Stored Procedure, and is impossible to change with case statements. I will have to change the User Interface via some setting in Management Studio.
May 30, 2006 at 10:38 am
Martin,
This is OK. You can still use Temp. Table and then use ISNULL or CASE with Stored procedure. There was a discussion some time ago on how to insert the resultset that is an output of the stored procedure into the temp. table. It is easy to do if you know the columns in advance with INSERT... EXECUTE. If you don't know the columns in advance, the following solution will work for you:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=206787&p=3
RE: Dynamically create table when executing a stored procedure
RGR'us suggested a very good solution with an openrowset that I modified a little for you to add ISNULL function to replace NULLs with empty string. I tried it. In this case Dbname field will contain NULLs and the last column will not contain NULLs in the Management Studio for sp_who as an example stored procedure:
Select
* into #T1
from
Openrowset('SQLOLEDB','Trusted_connection=yes;Data Source=''local''','exec sp_who') dt
select
*, ISNULL(dbname,' ')
from #T1
drop
table #T1
Please, give all credits to RGR'us
Regards,Yelena Varsha
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply