Storing The Result In A Variable Resulting From A Dynamic TSQL Query

Hello all:

Here is a sample query:



DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'

DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'



DECLARE @KeyCol NVARCHAR(50);

SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'


DECLARE @KeyValue NVARCHAR (1000)


SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +

' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '



The value resulting from Executing @KeyValue is an integer.

I want to store this value in a new variable say @VAR2

When I do this

DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue

its giving me an error.


can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?