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.