Dynamic evaluation

[NickName]

/* goal: dynamic evaluation of table row
platform: sql 2000
*/

use northwind;

declare @tbl sysname
set @tbl = 'customers'

EXEC('select count(*) from ' +@tbl)

/*
Why the following dynamic evaluation would fail
IF EXEC('select count(*) from ' +@tbl) = 0
PRINT 'no rows'

Its variant,
IF exists (EXEC('select count(*) from ' +@tbl)) = 0
PRINT 'no rows'
*/

Do I know more sleep :)

TIA.

[David Portas]

> Why the following dynamic evaluation would fail

IF EXEC('select count(*) from ' +@tbl) = 0

Because EXEC is a statement, not an expression.

You could insert the result of EXEC into a table

INSERT INTO foo (table_name, row_count)
EXEC('select '''+@tbl+''',co unt(*) from ' +@tbl)

and retrieve the value from there.

Or you could use sp_executesql to pass input and output parameters.

Or you could retrieve the rowcount from SYSINDEXES (based on index stats so
there is some latency between the physical rowcount and the count updated in
SYSINDEXES).

--
David Portas
SQL Server MVP
--

[Hugo Kornelis]

On 17 Jan 2005 12:32:34 -0800, NickName wrote:

/* goal: dynamic evaluation of table row
platform: sql 2000
*/

use northwind;

declare @tbl sysname
set @tbl = 'customers'

EXEC('select count(*) from ' +@tbl)

/*
Why the following dynamic evaluation would fail
IF EXEC('select count(*) from ' +@tbl) = 0
PRINT 'no rows'

Its variant,
IF exists (EXEC('select count(*) from ' +@tbl)) = 0
PRINT 'no rows'
*/

Do I know more sleep :)

TIA.

Hi Nick,

I think that the most important question is: why don't you know the names
of your tables? Dynamic SQL is very rarely necessary in a well designed
database. If you feel you must use dynamic SQL, then at least read up on
the dangers of SQL injection. What if somebody sets @tbl to 'customers
drop customers'? The exec will happily count the number of rows in the
customers table, then drop it. Read www.sommarskog.se/dynamic_sql.html.

Your query is also rather inefficient. Using COUNT(*) to find if there are
zero rows means that SQL Server will just spend the time to calculate the
exact amount of rows in your 30 GB table, only to find that it's not 0. If
you use EXISTS instead, SQL Server will stop searching as soon as the
first row is found.

Finally, your queries are syntactically wrong. EXECUTE() is a statement,
not an expression. And everything included between the parentheses runs in
it's own context, so it should be a complete batch. The code below would
run - but it's still susceptible to SQL injection attacks!

EXEC ('IF EXISTS (SELECT * FROM ' + @tbl + ') PRINT ''no rows''')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[NickName]

Thanks, David. The first option seems doable; as for the second one,
as you mentioned it does not seem to accurate.

Don

[NickName]

Hugo, here you go, assumption, assumption :) in this particular case,
we have several hundred tables for one db, one app needs ninety or so
tables from one (it has its ddl for tbl creation etc.), dts over data
from the master db. How do we verify that all ninety or so tables have
the data populated? Naturally, a script to check data in each table
would seem to make sense. And this script is not for public
consumption, yes, I'm sort of aware of sql injection attack, I don't
think it's applicable here though.

Thanks for the
EXEC ('IF not EXISTS (SELECT * FROM ' + @tbl + ') PRINT ''no rows''')
trick

The flow is not elegant though because I may need to do quite a bit
stuff after the evaluation and put them all together inside the EXEC
block seems pretty messy.

Also, good for you to refresh my memory about the speed between 'using
count(*)' and 'exists'.

Don

[Hugo Kornelis]

On 18 Jan 2005 06:55:29 -0800, NickName wrote:

Hugo, here you go, assumption, assumption :) in this particular case,

(snip)

Hi Don,

Good to hear that you're aware of the danger of SQL Injection and that
you're only using this dynamic SQL for a maintenance task.

I hope you didn't mind me bringing this up. I prefer warning about SQL
Injection even when the warning is not needed over not warning when I
should have. :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

Depends what you mean by accurate. Unless you can cease all inserts
(say by logging everyone out or by locking each table) the number of
rows in a table is a moving target, it's just a case of what is
acceptably up-to-date.

--
David Portas
SQL Server MVP
--

[NickName]

Agree, Hugo. Thank you. Don

[NickName]

I see your point, David, thanks. Don

[NickName]

d sql is a mess to me (the quotes!),
trying to change the following line
EXEC ('IF EXISTS (SELECT * FROM ' + @tbl + ') PRINT ''no rows''')
to
EXEC('If not exists(select * from '+ @tbl +') PRINT '+ @tbl +'''no
rows''')

got no where.