NULL & Count()

Question

NULL & Count()

karthik M

One Orange Chip

Points: 29139
More actions
April 23, 2008 at 7:38 am

#181597

Experts,
I have one senario.
create table #t1
(
Eno int,
Age int null
)
insert into #t1(Eno)
select 1
union all
select 2
union all
select 3
insert into #t1
select 4,5000
select * from #t1
Output:
Eno Age
----------- -----------
1 NULL
2 NULL
3 NULL
4 5000
declare @NULL int
declare @Nm varchar(50)
select @Nm = 'Age'
select @NULL = count(*) from #t1 where (@Nm) is null
select @NULL
is showing '0' instead of '3'.How ?
Inputs are welcome !
karthik

Viewing 15 posts - 1 through 15 (of 24 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Because the variable @Nm is not null. It is equal to 'Age'

If you want to have a variable column specified in the where clause, you'll have to use dynamic SQL, with all it's associated problems.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

select @Nm = 'Age'

select @NULL = count(*) from #t1 where (@Nm) is null

As "where (@Nm) is null" will always be false, zero rows will meet the

restriction.

Why are you expecting a value of 3 ?

SQL = Scarcely Qualifies as a Language

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Karthik -

You're trying to backdoor in some dynamic SQL without writing it up as dynamic SQL. It's not going to use the variable as a column name, it's simply going to evaluate it as a scalar value.

Set it up the right way, using dynamic SQL.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Carl Federl (4/23/2008)
Why are you expecting a value of 3 ?

He wants to specify the column name in a variable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Carl Federl
select @Nm = 'Age'
select @NULL = count(*) from #t1 where (@Nm) is null
As "where (@Nm) is null" will always be false, zero rows will meet the
restriction.

Why and how ? Please explain me.

Why are you expecting a value of 3 ?

Because Age column contains 3 NULL values. Simply i want to return the count of number of NULL in the Age column.

Matt Miller
it's simply going to evaluate it as a scalar value.

Can you explain it how ?

Whethere my code is to be considered as a dynamic sql or not .If not please provide me the proper query to achieve the same task.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

karthikeyan (4/23/2008)
Carl Federl
select @Nm = 'Age'
select @NULL = count(*) from #t1 where (@Nm) is null
As "where (@Nm) is null" will always be false, zero rows will meet the
restriction.
Why and how ? Please explain me.

Because the variable @Nm has the value 'Age'. Hence, it is not null.

You're asking for the number of rows where the variable has the value null, which is 0.

SQL does not dynamically evaluate column names like that. You will ahve to build up the SQL string and EXEC it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 7

karthik M

One Orange Chip

Points: 29139

April 23, 2008 at 8:30 am

#806090

Gila,

Can you tell me how to do it ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 8

declare @STR varchar(2000)

select @Nm = 'Age'

select @STR = 'select count(*) from #t1 where' +@Nm +' is null'

exec (@str)

am i correct ?

If yes,How to capture the output in a variable.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Look up sp_executesql. It allows you to pass parameters in and out of dynamic SQL. I know there's a good example in books online showing exactly that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 10

karthikeyan (4/23/2008)
Carl Federl
select @Nm = 'Age'
select @NULL = count(*) from #t1 where (@Nm) is null
As "where (@Nm) is null" will always be false, zero rows will meet the
restriction.
Why and how ? Please explain me.
Why are you expecting a value of 3 ?
Because Age column contains 3 NULL values. Simply i want to return the count of number of NULL in the Age column.
Matt Miller
it's simply going to evaluate it as a scalar value.
Can you explain it how ?
Whethere my code is to be considered as a dynamic sql or not .If not please provide me the proper query to achieve the same task.

Any Inputs ?

karthik

Ripal Patel SSChasing Mays Points: 629 More actions · Answer 11

use following query

declare @Query nvarchar(500),@parameters nvarchar(500)

set @Query='select @NULL = count(*) from #t1 where '+@Nm+' is null'

set @parameters='@Null int output'

exec sp_executesql @Query,@parameters,@Null=@Null output

print @NULL

insted of

select @NULL = count(*) from #t1 where (@Nm) is null

raja_saminathan SSCommitted Points: 1607 More actions · Answer 12

Hi Karthick,

I think the problem with your code is you have assigned age like a value for the @Nm, not Age as like a column containing the values

I tried and give my result ,hope it will help u

--in this scenario u have to use a table variable,I have Rewritten your code as follows

declare @NULL int

Declare @Nm table(Age Varchar(50))

insert into @Nm(Age)

select Age from #t1 where Age is null

Select @NULL= count(*) from @nm

select @NULL

2.

u can simply replace age in place of @Nm in the where condition it will give you the expected result too. Or you have to use a table variable as like above.

Regards,

Rajesh

karthik M One Orange Chip Points: 29139 More actions · Answer 13

Thanks Raja.

karthikeyan (4/23/2008)
--------------------------------------------------------------------------------
Carl Federl
select @Nm = 'Age'
select @NULL = count(*) from #t1 where (@Nm) is null
As "where (@Nm) is null" will always be false, zero rows will meet the
restriction.
Why and how ? Please explain me.
Why are you expecting a value of 3 ?
Because Age column contains 3 NULL values. Simply i want to return the count of number of NULL in the Age column.
Matt Miller
it's simply going to evaluate it as a scalar value.
Can you explain it how ?
Whethere my code is to be considered as a dynamic sql or not .If not please provide me the proper query to achieve the same task.

Can anybody explain it ?

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Yes, and I did. See the last post on page 1.

If there's anything you're still not sure of, ask specifically.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass