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 ...
CodePudding user response:
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
CodePudding user response:
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