Doing a count over a subquery

Question

We have an application which allows users to define queries. We would like to do a count over any possible subquery

e.g.

Configured SQL - Which works fine:

DROP TABLE IF EXISTS TempData
EXEC [StoredPRoc]
Select * from TempSurpacData

We then attempt a count over this query like so:

SELECT COUNT(*) FROM (
    DROP TABLE IF EXISTS TempData
    EXEC [StoredPRoc]
    Select * from TempSurpacData
) tbl 

This unfortunately fails ...

Answer 1

The DROP TABLE and EXEC statements should be outside the select statement:

DROP TABLE IF EXISTS TempData
EXEC [StoredPRoc]
SELECT COUNT(*) FROM (
    Select * from TempSurpacData
) tbl 

Or

DROP TABLE IF EXISTS TempData
EXEC [StoredPRoc]
Select count(*) from TempSurpacData

If you really can't do it this way you can take a look at this post

Answer 2

You have given very little info.

what [StoredPRoc] does ? Like [StoredPRoc] also return some result and Select * from TempSurpacData also return some result then you need count of which resultset.

we do make sure the user query returns rows prior to saving it.

when you can do this then surely you can find row count in same place, just we don't know what code is use there.

The first query I am not in control of this is entered by a user via textbox in our app

What do you mean by this ? Does user enter only DROP TABLE IF EXISTS TempData or your complete example.

This is just layman example.

declare @i varchar(500)='DROP TABLE IF EXISTS TempData
EXEC [StoredPRoc]
Select * from TempSurpacData'

exec(@i)
Select @@ROWCOUNT

Suppose in my exampe both EXEC [StoredPRoc] and Select * from TempSurpacData return rows then

Select @@ROWCOUNT

will give number of rows return by Select * from TempSurpacData