Dynamic Query Sum

Colonel Angus · March 29th, 2005, 12:35 PM

I am trying to get the sum of two dynamic queries
That is:

Code:

EXEC('SELECT COUNT(id) as subtotal1...) + EXEC('SELECT COUNT(id) as subtotal2...)

If I assign to a variable, say @total, I get errors or NULLs. Is there a workaround?

Thanks in advance for your comments

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

jbenson001 · March 29th, 2005, 02:57 PM

I couldn't get you code to work. How about trying this
declare @cnt1 int
declare @cnt2 int
declare @total int

select @cnt1 = (select count(*) from <table>)
select @cnt2 = (select count(*) from <table>)
select @total = @cnt1 + @cnt2
select @total

Colonel Angus · March 29th, 2005, 04:26 PM

That doesn't work with the dynamic aspect of it.

Code:

select @cnt1 = (EXEC('select count(*) from <table>)')

Gives an error, "Incorrect syntax near 'EXEC'"

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

jbenson001 · March 29th, 2005, 04:28 PM

i am getting the error too.. I am working on it.. let me know if you get it.. and i will do the same

Colonel Angus · March 30th, 2005, 11:08 AM

I think this may be inefficient, but I gave this a try:

Code:

declare @total int
declare @tot1 int
declare @tot2 int

CREATE TABLE #table1 (tot int)
INSERT INTO #table1 EXEC('SELECT COUNT(C.id)...')

CREATE TABLE #table2 (tot int)
INSERT INTO #table2 EXEC('SELECT COUNT(C.id)...')

SELECT @tot1 = tot FROM #table1
SELECT @tot2 = tot FROM #table2

SELECT @total = @tot1 + @tot2
RETURN @total

This didn't give any errors, but I'm having trouble getting it to produce results. In fact, my query appears to hang on it. Hope it's a start.

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.