Very Computer

I need to find out how many rows are returned by a subquery that i'm
executing thru dynamic sql. I will be either using the subquery to return
rows that I then insert into a table as a "batch", or allow the user to
preview how many would be inserted, if they ran it in "production mode".

example:

Test Mode:
I can't do : SELECT COUNT(*) FROM (SELECT somevalue FROM somedb..sometable)

Production Mode:
INSERT INTO somelocaltable
( collist)
(SELECT somevalue FROM somedb..sometable)

Again, I'm executing these statements with sp_executesql, since I'm building
the SQL and pointing to variable databases.

Todd

Quote:> I can't do : SELECT COUNT(*) FROM (SELECT somevalue FROM
somedb..sometable)

Assuming that "somevalue" is the name of a column on your table:

SELECT COUNT(somevalue)
  FROM somedb..sometable;

Joe

The inner SQL cannot be touched. Imagine letting a user specify the query to
create a batch, as long as it only returns a list of record IDs, in the
order that they want them batched. That's why I couldn't touch the inner SQL
and had to excute it in dyn SQL. I finally figured out how to do it. I had

OUTPUT var with sp_executesql.

As it turned out, the "production run" that didn't just want a count, but
also to insert the values was also tricky. I had to walk a cursor in the dyn
sql, inserting each record one at a time. Yucky, but it works and I'm happy.

It's always so tricky to try to explain this stuff in such a forum.

Quote:> Todd

> > I can't do : SELECT COUNT(*) FROM (SELECT somevalue FROM
> somedb..sometable)

> Assuming that "somevalue" is the name of a column on your table:

> SELECT COUNT(somevalue)
>   FROM somedb..sometable;

> Joe