CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
El.Hum
El.Hum

Reputation: 1485

How to have a NULL instead of No Value from a query

I had a query like:

SELECT ISNULL(S.Name+'.'+T.Name,'Table Not Found')
FROM DataProfile.Tables T
INNER JOIN DataProfile.Schemas S ON T.schemaId=S.Id
WHERE S.Name+'.'+T.Name=@TableName

Then I tried

IIF(LEN(S.Name+'.'+T.Name)>0,S.Name+'.'+T.Name,NULL)

But when it doesn't find the named table returns not output, Value or Null value or anything I can work on. This is going to be used as a crosscheck.
Does anybody have any idea?

Upvotes: 4

Views: 152

Answers (4)

avb
avb

Reputation: 1753

this will always return a row:

select v.TableName, ISNULL(found.result, 'not found') result
from (values(@TableName))v(TableName)
    outer apply (
        select CAST('found' as nvarchar(11)) result
        from DataProfile.Tables T
            join  DataProfile.Schemas S ON T.schemaId=S.Id
        where S.Name+'.'+T.Name=v.TableName
    )found

Upvotes: 3

El.Hum
El.Hum

Reputation: 1485

Thanks for those who payed attention to what I exactly asked and for their responses. Here the way I tried:

DECLARE @Check NVARCHAR(MAX) = 'TABLE DOES NOT FOUND'

SELECT @Check= S.Name + '.' + T.Name
FROM DataProfile.Tables T
INNER JOIN DataProfile.Schemas S ON T.schemaId=S.Id
WHERE S.Name+'.'+T.Name=@TableName

SELECT @CHECK

And That Worked for me

Upvotes: 4

gmiley
gmiley

Reputation: 6604

Try doing it this way:

with table_qry as
(
    select S.Name as SName, T.Name as TName
    from DataProfile.Tables T
    inner join DataProfile.Schemas S 
    on T.SchemaId = S.Id
    where S.Name+'.'+T.Name = @TableName
)
select case when (select count(1) from table_qry) > 0 then
    SName+'.'+TName else 'Table Not Found' end as TableName
from table_qry;

There are more elegant ways of doing it, but this should work just fine for you.

Upvotes: 1

I A Khan
I A Khan

Reputation: 8839

you should try this 

SELECT CASE WHEN (LEN(S.Name + '.' + T.Name))> 1 THEN S.Name + '.' + T.Name ELSE NULL END

So you qry will look like 

SELECT CASE 
       WHEN (LEN(S.Name + '.' + T.Name))> 1 THEN 
          S.Name + '.' + T.Name 
       ELSE 
          NULL -- Here use any expresion which you want
       END
FROM DataProfile.Tables T
INNER JOIN DataProfile.Schemas S ON T.schemaId=S.Id
WHERE S.Name+'.'+T.Name=@TableName

Here you are using ISNULL(S.Name+'.'+T.Name,'Table Not Found') which never return false part due to if S.Name and T.Name both are null then still that value will be '.'

Upvotes: 1

Related Questions