Return string value when fields are null

Question

Post reply

Return string value when fields are null

byrdmom2

SSC Enthusiast

Points: 160
More actions
September 28, 2010 at 6:26 am

#227090

This is my stored procedure:
declare @t1 table
(
eForm53_GUID uniqueidentifier,
b datetime,
fullname varchar(100),
IsRemoved bit,
dtSubmitted datetime
)
insert into @t1
select eForm53_GUID,RecCreatedDate,fullname,IsRemoved, dtsbm from myMainTable where
(IsRemoved = 0 OR IsRemoved IS NULL) AND (dtsbm IS NULL) OR (dtsbm = '')
--***insert info from mySecondary into table variable
declare @t2 table
(
eForm53GUID uniqueidentifier,
b datetime,
c varchar(500),
TravelDateIsRemoved bit
)
insert into @t2
SELECT eForm53GUID,TDInclFromDate, TDDestCity,TravelDateIsRemoved FROM mySecondaryTable
where TravelDateIsRemoved is null or TravelDateIsRemoved = 0
--***get the information from both tables and concatenate date and city
select t1.eForm53_GUID, t1.fullname, convert(varchar(10),b,101) as RecCreatedDate,
(select convert(varchar(10),b,101) + ' ' + c as 'data()' from
@t2 t2 where t2.eForm53GUID = t1.eForm53_GUID for xml path('')) as TDInclFromDate
from @t1 t1
order by RecCreatedDate desc
I want to be able to have the TDInclFromDate column be set to 'No Dates' if the values are null. How do I accomplish this?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Try this :

--***get the information from both tables and concatenate date and city

select t1.eForm53_GUID, t1.fullname, convert(varchar(10),b,101) as RecCreatedDate,

ISNULL ( (select convert(varchar(10),b,101) + ' ' + c as 'data()' from

@t2 t2 where t2.eForm53GUID = t1.eForm53_GUID for xml path('')) ) , 'No Dates') as TDInclFromDate

from @t1 t1

order by RecCreatedDate desc

I just wrapped the TDInclFromDate with ISNULL ( column data , 'No Dates' ) ..

byrdmom2 SSC Enthusiast Points: 160 More actions · Answer 2

I get the following error message:

The isnull function requires 2 argument(s).

WayneS SSC Guru Points: 95460 More actions · Answer 3

byrdmom2 (9/28/2010)
I get the following error message:
The isnull function requires 2 argument(s).

CC didn't count the parenthesis correctly. This should work:

--***get the information from both tables and concatenate date and city

select t1.eForm53_GUID, t1.fullname, convert(varchar(10),b,101) as RecCreatedDate,

ISNULL ( (select convert(varchar(10),b,101) + ' ' + c as 'data()' from

@t2 t2 where t2.eForm53GUID = t1.eForm53_GUID for xml path('')) , 'No Dates') as TDInclFromDate

from @t1 t1

order by RecCreatedDate desc

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Why are you pulling results from your two source tables into table variables before using them in your final query? This is a shedload of extra code (trivial) and could seriously hit performance (very bad).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

byrdmom2 SSC Enthusiast Points: 160 More actions · Answer 5

I got it! Somehow something was misplaced when I tested it. But you had it right!!!! Thanks so much!

Lmred

byrdmom2 SSC Enthusiast Points: 160 More actions · Answer 6

I'm sure I'd be pushing my luck if I could also change the color of the text when the value was null! It would be nice.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

--***get the information from both tables and concatenate date and city

select

t1.eForm53_GUID,

t1.fullname,

convert(varchar(10), b, 101) as RecCreatedDate,

(select convert(varchar(10), b, 101) + ' ' + c as 'data()'

--from (

--SELECT eForm53GUID, TDInclFromDate, TDDestCity, TravelDateIsRemoved

FROM mySecondaryTable t2

where t2.TravelDateIsRemoved is null or t2.TravelDateIsRemoved = 0

--) t2

where t2.eForm53GUID = t1.eForm53_GUID

for xml path('')) as TDInclFromDate

from myMainTable t1

where (IsRemoved = 0 OR IsRemoved IS NULL)

AND (dtsbm IS NULL) OR (dtsbm = '') -- this doesn't look right

--AND (dtsbm IS NULL OR dtsbm = '') -- should it be this?

order by RecCreatedDate desc

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

byrdmom2 SSC Enthusiast Points: 160 More actions · Answer 8

You're right! No need for the table variables. Your query works fine and I will replace mine. Is there a way to change the color when I replace the null values?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

byrdmom2 (9/28/2010)
You're right! No need for the table variables. Your query works fine and I will replace mine. Is there a way to change the color when I replace the null values?

I've absolutely no idea. So long as the correct number of rows are returned with the correct values in the columns, they could be green with purple spots!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

byrdmom2 SSC Enthusiast Points: 160 More actions · Answer 10

byrdmom2

SSC Enthusiast

Points: 160

September 28, 2010 at 7:03 am

#1228304

😎 I'll have to do it on the client side. Thanks!