IsNull Evaluation within EXEC

[NickName]

Hi,

I seemed to me IsNull Evaluation within EXEC fails. Here's some more
detail of the problem.

-- goal: provide one parameter (of various value) to generate a
-- report
declare @col4 varchar(30)

select @col4 = null
-- pls note, the @col4 var is default to null but may likely
-- have a value

exec ('select col1, col2, col3, -- next dynamic col
' + @col4 + ',
col5, col6
count(*) as total
from FACT_TBL
where 1=1
-- THE FOLLOWING CONDITION EVALUATION FAILED
-- in the sense that if the parameter is not called query does
-- not return any result set, which is wrong
and COL4 = IsNull('''+@COL4+''',COL4)
group by '+@COL4+', col5
')

[Razvan Socol]

Hi,

See: http://www.sommarskog.se/dyn-search.html

Razvan

[Erland Sommarskog]

NickName (da****@rock.com) writes:

I seemed to me IsNull Evaluation within EXEC fails. Here's some more
detail of the problem.

-- goal: provide one parameter (of various value) to generate a
-- report
declare @col4 varchar(30)

select @col4 = null
-- pls note, the @col4 var is default to null but may likely
-- have a value

exec ('select col1, col2, col3, -- next dynamic col
' + @col4 + ',
col5, col6
count(*) as total
from FACT_TBL
where 1=1
-- THE FOLLOWING CONDITION EVALUATION FAILED
-- in the sense that if the parameter is not called query does
-- not return any result set, which is wrong
and COL4 = IsNull('''+@COL4+''',COL4)
group by '+@COL4+', col5
')

This can never work. Normally concatenation with NULL yields a NULL value,
so all you would get is EXEC(NULL). However, that rull does not seem to
apply to EXEC(), so instead the NULL value is substituted with the
empty string, leaving you with isnull('', COL4) of which the result is
''.

You could save the show with

nullif(IsNull('''+@COL4+''', ''''), COL4)

Then again, if @COL4 is NULL, then the SELECT list and the GROUP BY
clause will give you a syntax error.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[NickName]

Ahe, thanks, but I did no formulate the problem exactly. It should
really be written as the following:

-- goal: provide one parameter (of various value -- any of a provided
list of columns) to generate a
-- report, ONE column selection is mandatory or it's a mandatory param
declare @col4 varchar(30),
@colX varchar(30), -- optional condition
@colY int -- optional condition

select @col4 = null
-- pls note, the @col4 var is default to null but may likely
-- have a value

exec ('select col1, col2, col3, -- next dynamic col
' + @col4 + ',
col5, col6
count(*) as total
from FACT_TBL
where 1=1
-- THE FOLLOWING CONDITION EVALUATION FAILED
-- in the sense that if the parameter is not called query does
-- not return any result set, which is wrong
and colX = IsNull('''+@colX+''',colX)
and colY = IsNull('''+@colY+''',colY)
/* REPLACING THE ABOVE two conditions with
and colX = NullIf(IsNull('''+@colX+''',''''),colX)
and colY = NullIf(IsNull('''+@colY+''',''''),colY)
does not seem to help. There must be a way to do this, giving user a
choice to pick one column from many while making at least one or two
columns mandatory (default) with a sp.
*/
group by '+@COL4+', col5
')

Many thanks as usual.

[Erland Sommarskog]

NickName (da****@rock.com) writes:

Ahe, thanks, but I did no formulate the problem exactly. It should
really be written as the following:

-- goal: provide one parameter (of various value -- any of a provided
list of columns) to generate a
-- report, ONE column selection is mandatory or it's a mandatory param
...
/* REPLACING THE ABOVE two conditions with
and colX = NullIf(IsNull('''+@colX+''',''''),colX)
and colY = NullIf(IsNull('''+@colY+''',''''),colY)
does not seem to help. There must be a way to do this, giving user a
choice to pick one column from many while making at least one or two
columns mandatory (default) with a sp.
*/

Now there is a @colX and a @colY. I think that I understand less
than before. Thus, I resort to the standard recommendaton that you
include:

o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result given the sample.

For this case you would also have to supply the desited interface
for your stored procedure, as well as an example on you intend to call it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[NickName]

Sounds fair. Please see below. Thanks alot.

/*
-- ENV: MS SQL Server 2000; current db: northwind
-- ddl
create table #myTBL (col1 int, col2 varchar(10), col3 bit, col4
datetime);

-- dml: data population
insert into #myTBL
values(1,'abc',0,getDate());

insert into #myTBL
values(2,'efg',1,'01/02/2004');

insert into #myTBL
values(3,'hij',0,'03/12/2004');

insert into #myTBL
values(4,'klm',0,'02/14/2004');

insert into #myTBL
values
(1,'nlo',0,'05/14/2004');

insert into #myTBL
values
(3,'opq',1,'08/24/2004')
*/

/*
-- dml: get sample data
select *
from #myTBL

-- result set
col1 col2 col3 col4

----------- ---------- ----
------------------------------------------------------
1 abc 0 2005-04-25 11:29:48.473
2 efg 1 2004-01-02 00:00:00.000
3 hij 0 2004-03-12 00:00:00.000
4 klm 0 2004-02-14 00:00:00.000
1 nlo 0 2004-05-14 00:00:00.000
3 opq 1 2004-08-24 00:00:00.000
*/

/* business case/requirements:
a) provide one mandatory parameter (of selecting one column from a
few);
b) provide optional conditional statement (such as col3)
*/

-- sql stmt to support the above objective

declare @dCOL varchar(30), @col3 bit -- optional condition

select @dCOL = 'col1'
-- pls note, the @dCOL var is default to null but MUST have a value
select @col3 = null
/*
select @col3 = 0
select @col3 = 1
NOTE:
set select @col3 = null
or
select @col3 = 0
PRODUCES SAME RESULTSET, which means
NULL and 0 are treated the same, hmm, how come?
*/

exec ('select ' + @dCOL + ', col3, count(*) as total
from #myTBL
where 1=1
and col3 = IsNull('''+@col3+''',col3)
-- NullIf(IsNull('''+@col3+''',''''),col3)
group by '+@dCOL+', col3
')

[Erland Sommarskog]

NickName (da****@rock.com) writes:

/*
select @col3 = 0
select @col3 = 1
NOTE:
set select @col3 = null
or
select @col3 = 0
PRODUCES SAME RESULTSET, which means
NULL and 0 are treated the same, hmm, how come?
*/

exec ('select ' + @dCOL + ', col3, count(*) as total
from #myTBL
where 1=1
and col3 = IsNull('''+@col3+''',col3)
-- NullIf(IsNull('''+@col3+''',''''),col3)
group by '+@dCOL+', col3
')

I'm still not sure that I understand - you forgot to include
expected results for various inputs - but instead of solving
everything in one SQL String, why not build the SQL string
piece by and add extra conditions as needed?

Since col3 is a bit, and you isnull plays with string, things
are likely to be messy.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[NickName]

"you isnull plays with string", Interesting point that I previously
wasn't aware of, however, my following testing seems to prove that even
with pure text strings, NULL evaluation within EXEC still fails.
Please see below. TIA.
/*
-- ddl
create table #myTBL (col1 int, col2 varchar(10), col3 bit, col4
datetime, col5 varchar(20));

-- dml: data population
insert into #myTBL
values(1,'abc',0,getDate(),'zzyyxx');

insert into #myTBL
values(2,'efg',1,'01/02/2004','ttssrr');

insert into #myTBL
values(3,'hij',0,'03/12/2004','rrppoo');

insert into #myTBL
values(4,'klm',0,'02/14/2004','qqppoo');

insert into #myTBL
values
(1,'nlo',0,'05/14/2004','nnmmll');

insert into #myTBL
values
(3,'opq',1,'08/24/2004','kkjjii')
*/

/*
-- dml: get sample data
select *
from #myTBL

-- result set
col1 col2 col3 col4
col5
----------- ---------- ----
------------------------------------------------------
--------------------
1 abc 0 2005-04-26 09:29:16.817
zzyyxx
2 efg 1 2004-01-02 00:00:00.000
ttssrr
3 hij 0 2004-03-12 00:00:00.000
rrppoo
4 klm 0 2004-02-14 00:00:00.000
qqppoo
1 nlo 0 2004-05-14 00:00:00.000
nnmmll
3 opq 1 2004-08-24 00:00:00.000
kkjjii

(6 row(s) affected)
*/

/* business case/requirements:
a) provide one mandatory parameter (of selecting one column from a
few);
b) provide optional conditional statement (such as col3)
*/

-- sql stmt to support the above objective

declare @dCOL varchar(30), @col5 varchar(20) -- optional condition

select @dCOL = 'col1'
-- pls note, the @dCOL var is default to null but MUST have a value
select @col5 = 'ttssrr'
-- select @col5 = null

exec ('select ' + @dCOL + ', col5, count(*) as total
from #myTBL
where 1=1
and col5 = IsNull('''+@col5+''',col5)
group by '+@dCOL+', col5
')

/*
-- execution result of the above stmt with @col5 set to 'ttssrr'
col1 col5 total
----------- -------------------- -----------
2 ttssrr 1
*/

/*
-- execution result of the above stmt with @col5 set to null
col1 col5 total
----------- -------------------- -----------

Expected result:
6 rows (for the col5 condition would be "and col5 = col5")
*/

[NickName]

Erland,

Just an update, I figured it out. :)

Don