Grab results of exec? 1

[jmj]

Im using query analyzer to send results to a file. I have a series of queries that need a variable input.
I want to declare the variable at the top of the page depending on an id. I'm not sure how to get past this point.
Declare @Total int
SET @Total='Select count(*) FROM tbl_A Where ID=3'
exec(@Total)
The exec @Total shows the number I need.
Now I need to grab that number and set it to a variable called @TotR
Then I want to use @TotR in my select queries:
Select Var1/@TotR as 'Divide'
From table1
What am I missing?? I assume it's something easy....
Thanks,
J

 

[vongrunt]

> Declare @Total int
> SET @Total='Select count(*) FROM tbl_A Where ID=3'

Are you sure?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLBill]

Variables only exist for the scope of their query. @Total exists for your exec statement, but not for the rest. I suggest that you put the value into a global temporary table (##table_name). Then that value can be used outside the scope of the query that created it.

But an important question is why are you even using @Total? Why not just start with @TotR? Your current script gets a value and puts it into @Total then puts it into @TotR. That's duplication of effort.

SELECT COUNT(*) AS MyCount INTO ##mytemptable
FROM tbl_A
WHERE ID=3
GO
DECLARE @TotR INT
SET @TotR = (SELECT MyCount FROM ##mytemptable)
<rest of script>
GO
DROP ##mytemptable
GO

Or even just select the value from the temp table in each script.

-SQLBill




Posting advice: FAQ481-4875

 

[SQLBill]

Did you hear that Vongrunt??? That was the sound of my hand slapping my forehead. You posted while I was still typing...completely missed that syntax issue - however I used the correct syntax in my suggestion.

-SQLBill

Posting advice: FAQ481-4875

 

[jmj]

Ooops that should be varchar (that's what I get for typing vs. copy/paste.
But yes I need this as the overall total. (The ID will actually be a loop)

Declare @Total varchar(100)
> SET @Total='Select count(*) FROM tbl_A Where ID=3'

 

[vongrunt]

> That was the sound of my hand slapping my forehead.

I prefer using brick wall, as in . That way only head hurts; I need hand for typing

Btw. why not simply:

declare @blah int
select @blah = count(*) FROM tbl_A Where ID=3

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jmj]

declare @blah int
select @blah = count(*) FROM tbl_A Where ID=3
I kept getting errors trying to get the actual count trying to do it this way. It reads it as the actual string, not as a query statement.

 

[vongrunt]

Post code here...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)