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+''',count(*) 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.

[Hugo Kornelis]

On 18 Jan 2005 10:16:18 -0800, NickName wrote:

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.

Hi Don,

Yep, all that doubling of quotes can give you quite a headache. Try

EXEC('If not exists(select * from '+ @tbl +') PRINT'''+ @tbl+' no rows''')

Best, Hugo
--

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

[NickName]

Thank you, Hugo, you're the man !

Let me see if I get it (how the quotes work).
Start from the most outer ones:
the first, ' -- EXEC stmt requires that, this is the start
the second, that is, '+ @tbl +' -- separate var from strings
the third, that is, PRINT '' -- command inside EXEC needs double
quotes, this one is the start
the fourth, that is, '+ @tbl+' --- same as the second
the fifth, that is, '', -- close the PRINT command
the sixth, that is, ' -- close for the EXEC stmt
Is the above analysis correct?

TIA.

Don

[Hugo Kornelis]

On 18 Jan 2005 13:39:53 -0800, NickName wrote:

(snip)

Is the above analysis correct?

Hi Don,

Yep!

Best, Hugo
--

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

[--CELKO--]

You could go to the system tables, pull out all the names and use a
text editor to make a script to do this. Once you have seen which
tables are not used, you can drop them.

I am curious about these empty tables. The last place I saw this as
such a common problem that people needed to have a utility program to
detect them like you are, it was because of a totally screwed up design
by an OO programmer.

He thought that you should create and drop tables like object
instances. The result was a lot of orphans floating around until the
database choked on them. Abandoned shopping cart = empty table with
generated name.

[Hugo Kornelis]

On 18 Jan 2005 19:18:52 -0800, --CELKO-- wrote:

I am curious about these empty tables. The last place I saw this as
such a common problem that people needed to have a utility program to
detect them like you are, it was because of a totally screwed up design
by an OO programmer.

Hi Joe,

This case is different. Don already explained why he needs this code in a
previous message, when I questioned his use of dynamic SQL. Here's a quote
from that message:

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?

Best, Hugo
--

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

[NickName]

Hugo, thanks for the confirmation. Don

[NickName]

Joe, yes, I built a utility program using a system table to do just
that with Hugo's help on d sql. Don

[NickName]

Hi Hugo,

I still don't have a solid grip over d sql yet. Here's another case,

-- goal: drop empty tables from current db and copy data from another
db
-- on the same server

-- the usual stuff omitted here
EXEC('If not exists(select * from '+ @tbl +') DROP table '+ @tbl'
select into '+ @tbl +' from anotherDBname.'+ @tbl'')

-- err msg
Incorrect syntax near ' select into '

TIA

Don

Hugo Kornelis wrote:

On 18 Jan 2005 19:18:52 -0800, --CELKO-- wrote: ....>
Best, Hugo
--

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

[Hugo Kornelis]

On 28 Jan 2005 07:08:28 -0800, NickName wrote:

-- the usual stuff omitted here
EXEC('If not exists(select * from '+ @tbl +') DROP table '+ @tbl'
select into '+ @tbl +' from anotherDBname.'+ @tbl'')

-- err msg
Incorrect syntax near ' select into '

Hi Don,

Looks like you left out an + sign and included two unneeded 's.

EXEC('If not exists(select * from '+ @tbl +') DROP table '+ @tbl +' select
into '+ @tbl +' from anotherDBname.'+ @tbl)

Best, Hugo
--

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

[NickName]

Hi Hugo,

I did exactly what you suggested above, and yet
strange, still get this err
Incorrect syntax near the keyword 'into'.

TIA

Don

[Hugo Kornelis]

On 28 Jan 2005 14:52:48 -0800, NickName wrote:

I did exactly what you suggested above, and yet
strange, still get this err
Incorrect syntax near the keyword 'into'.

Hi Don,

Do you have a space between the DROP tablename and the SELECT INTO? If
not, the generated SQL will be DROP tablenameSELECT INTO - you'll attempt
to drop a table named "tablenameSELECT" and the next command (INTO)
generates the error message.

Oh, BTW - don't use IF EXISTS (SELECT * FROM table) to check if the table
exists - it will cause an error if it doesn't and it will evaluate to
false if the table does exist, but has no rows. The table won't be
dropped, and the subsequent attempt to use SELECT INTO will fail.
Instead, use IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME = 'tablename'). (This can even be used without dynamic SQL!)

Best, Hugo
--

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

[Erland Sommarskog]

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

I did exactly what you suggested above, and yet
strange, still get this err
Incorrect syntax near the keyword 'into'.

Don't say:

EXEC('If not exists(select * from '+ @tbl +') DROP table '+ @tbl'
select into '+ @tbl +' from anotherDBname.'+ @tbl'')

Say:

DECLARE @sql
SELECT @sql =
'If not exists(select * from '+ @tbl +') DROP table '+ @tbl'
select into '+ @tbl +' from anotherDBname.'+ @tbl''
PRINT @sql
EXEC(@sql)

Then you see what SQL you have generated, and you should be able to
fix obvious syntax errors.

In this case the problem is more that you don't seem to know the syntax
for SELECT INTO. Here, Books Online, is something you should start using.
By looking up the syntax in Books Online, you can get the answer instantly,
and don't have to wait for someone to answer a question on the newsgroup.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Hugo Kornelis]

On Sat, 29 Jan 2005 15:52:11 +0000 (UTC), Erland Sommarskog wrote:

In this case the problem is more that you don't seem to know the syntax
for SELECT INTO.

Drat! I simply CAN'T believe I missed that....

I guess it was just a bit too obvious....

Best, Hugo
--

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

[NickName]

Good point about approach, Erland, however, still problem with
quote(s),
err msg = Incorrect syntax near '
select into '.
the following portion of the code seems tough
'If not exists(select * from '+ @tbl +') DROP table '+* @tbl'
select into '+ @tbl +' from anotherDBname.'+ @tbl''
Thanks.

Don

[NickName]

Good point about approach, Erland, however, still problem with
quote(s),
err msg = Incorrect syntax near '
select into '.
the following portion of the code seems tough
'If not exists(select * from '+ @tbl +') DROP table '+* @tbl'
select into '+ @tbl +' from anotherDBname.'+ @tbl''
Thanks.

Don

[Hugo Kornelis]

On 31 Jan 2005 07:04:14 -0800, NickName wrote:

Good point about approach, Erland, however, still problem with
quote(s),
err msg = Incorrect syntax near '
select into '.
the following portion of the code seems tough
'If not exists(select * from '+ @tbl +') DROP table '+* @tbl'
select into '+ @tbl +' from anotherDBname.'+ @tbl''
Thanks.

Hi Don,

You re-introduced an old error. Allow me to copy and paste from one of my
previous messages:

Looks like you left out an + sign and included two unneeded 's.

EXEC('If not exists(select * from '+ @tbl +') DROP table '+ @tbl +' select
into '+ @tbl +' from anotherDBname.'+ @tbl)

Or, after following Erland's suggestions:

DECLARE @sql varchar(500)
SELECT @sql =
'If not exists(select * from '+ @tbl +') DROP table '+ @tbl +'
select into '+ @tbl +' from anotherDBname.'+ @tbl
PRINT @sql
EXEC(@sql)

This should fix the quote-balancing. You still need to lookup the correct
syntax for select into, you need to do something about the error you'll
get when the table doesn't exist and you need to address the error you'll
get if the table isn't empty.

Best, Hugo
--

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

[Erland Sommarskog]

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

Good point about approach, Erland, however, still problem with
quote(s),
err msg = Incorrect syntax near '
select into '.
the following portion of the code seems tough
'If not exists(select * from '+ @tbl +') DROP table '+* @tbl'
select into '+ @tbl +' from anotherDBname.'+ @tbl''

Well, let me perfectly honest. If you want to play with dynamic SQL, you
must be able to handle quotes and nesting of quotes. It's not very effecient
programming to run to a newsgroup as soon as you get an error message
from SQL Server.

Yes, looking at strings like this is a bit confusing, and it's easy to
lose focus. The colour coding in Query Analyzer does help to some
extent.

And, yes, error messages from SQL Server are not always that super-clear.
But in this case, it's giving you the token that violates the syntax,
and the token is a character literal.
--
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]

Sorry, Hugo, I did not indicate the goal clearly, which is, first I'd
like to check if the source db table has any rows, if not, copy data
from target db's same table.

Thanks.

Don

[Hugo Kornelis]

On 31 Jan 2005 07:39:03 -0800, NickName wrote:

Sorry, Hugo, I did not indicate the goal clearly, which is, first I'd
like to check if the source db table has any rows, if not, copy data
from target db's same table.

Hi Don,

That's not what your current code will do. Once you get the quotes fixed,
it will check to see if there are rows; if the table doesn't exist, you'll
get an error; if the table exists and is empty, it will be dropped and if
it is not empty, it won't be dropped.
Then, regardless of whether there were rows or not, the select into will
be executed. If there were rows, the table isn't dropped and the select
into will fail.

I think you need to use a BEGIN END block to do what you want. But another
question is: why drop and recreate the table when it's empty? Why not use
INSERT ... SELECT, to insert into an existing table?

Best, Hugo
--

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

[NickName]

Hi Hugo,

I was MINDLESS. Can't believe it, forgot the simple syntax of SELECT *
into anewTBL from an existingTBL.
It's working now.

Many thanks, I owe you 5 bottles of beers now :)

Don

[NickName]

Yes, Hugo, the BEGIN END block is necessary and I added that, but did
not say so last time. I chose SELECT INTO vs. INSERT INTO for the
reason that if the target table (supposedly same schema as the source
one) changes its schema, insert would fail while SELECT INTO would copy
over attribute definitions and data.
Sorry, I did not make everything clear up front.

Many thanks.

Don

[NickName]

Concur, Erland, and thanks.

Don