How To: Retrieve A Value From An Exececuted Query?

I would like to use a dynamic query to obtain a value...a recordcount specifically.





I have two options so far. The 2nd option works fine, but seems like it would be slower. The first option doesn't recognize that i have declared my variable (@int). Any ideas how i would use the first method...or if it's worth trying?





Method 1





--# produces error: Must declare the variable '@int'.





DECLARE @int as int


EXEC ('SELECT @int = COUNT(ProductID) FROM Products')


print @int








Method2





--# works fine...but seems less efficient.


EXEC ('SELECT ProductID FROM Products')


print @@rowcount