SP_EXECUTESQL / Dynamic TSQL

Here is the query..
@ENTITY, @ FIELD, @KEYID, @VALUE comes dynamically using cursor. Here in this example I have took one sample and assigned it a value to do sanity check.



DECLARE @ENTITY nvarchar (100)

SET @ENTITY ='AccidentDimension'


DECLARE @FIELD nvarchar (100)

SET @FIELD = 'UHReceivedDate'


DECLARE @KEYID nvarchar (100)

SET @KEYID = '1074958'


DECLARE @VALUE varchar (100)

SET @VALUE = '10JAN2020'


DECLARE @FLAG NVARCHAR(50);

SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';


DECLARE @KeyName NVARCHAR(50);

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



DECLARE @KeyValue INT

DECLARE @SQL1 NVARCHAR (1000)

SET @SQL1 = ' SET @KeyValueOUT = Select '+ @KeyName + ' FROM ClaimManagementFact WHERE ClaimKey = ' + @KEYID +

' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(IncidentOnlyClaimCount) > 0 )'


EXECUTE sp_executesql @SQL1, @KeyValueOUT INT OUTPUT;


@KeyValue= @KeyValueOUT OUTPUT;


Select @KeyValue

A) What i want to do is store the value resulting from select statemenet by executing @SQL1 which is INT to @KeyValue. In previous thread I tried various thing but resulting in errors.

Thanks in advance for help