How to Set Value to Variable using Execute in T-SQL

Create table #TBL_PINDETAILS

(

PinCode varchar(6),

AreaCovered varchar(500)

)

Insert Into #TBL_PINDETAILS values('110001','New Delhi HO, Shastri Bhawan')

Insert Into #TBL_PINDETAILS values('110002','Indraprastha HPO, Ajmeri Gate Extn')

Insert Into #TBL_PINDETAILS values('110003','C G O Complex, Delhi High Court')

Insert Into #TBL_PINDETAILS values('110004','Rashtrapati Bhawan')

now,

DECLARE @QUERY VARCHAR(MAX)

SET @QUERY='SELECT PinCode FROM #TBL_PINDETAILS WHERE AREACOVERED=''New Delhi HO, Shastri Bhawan'''

EXEC (@QUERY)

It will return you result as 110001. Now I want to take this value from any variable. I can not set direclty like

SET @RESULT=EXEC (@QUERY).

It will give error.Its bit typical to set value in any variable from Exec directly.

now,

DECLARE @QUERY VARCHAR(MAX)
DECLARE @RESULT VARCHAR(20)
SET @QUERY='SELECT PinCode FROM #TBL_PINDETAILS WHERE AREACOVERED=''New Delhi HO, Shastri Bhawan'''

DECLARE @PinCode TABLE (pin varchar(10))
INSERT @PinCode
EXEC (@QUERY)
SELECT @RESULT=pin from @PinCode
SELECT @RESULT AS RESULT

First of all You have to create a table variable with a column pin (varchar type). After that insert value in table variable now set value in given variable with select query. I think this blog is helpful. Thanks for reading.