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

This blog describes How to set value in any variable using Execute in SQL Server : 

Please go through with below SQL QUERIES : -

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.

Ebook Download
View all
Learn
View all