Stored Procedure returns error

See more:

C#

I have created an stored procedure to import from excel file to store into a temporary table and update certain fields based on the relation provided in the relationship table.
I'm still struggling with
SET @QUERY=N'SELECT '+@FIELD+'FROM '+QUOTENAME(@TABLE_NAME )+'WHERE '+@REFER_FIELD+'='+@DUMP_VALUE+''
EXEC(@QUERY)
Returns my result for the next processing .but it cannot be used since the EXEC cause to exit from sp.I want the result from that query for further processing.Please let me know anyway....

Here is my updated SP

/****** Object: StoredProcedure [dbo].[sp_test] Script Date: 01/07/2014 09:31:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery1.sql|7|0|C:\Users\Accity\AppData\Local\Temp\~vs3E5F.sql
ALTER procedure [dbo].[sp_test] @DUMP_FIELD VARCHAR(20)='',@FIELD NCHAR(20)='' OUTPUT,@REFER_FIELD NCHAR(20)='' OUTPUT,@TABLE NCHAR(20)='' OUTPUT,@FLAG INT=0,
@TABLE_NAME SYSNAME='',@DUMP_VALUE VARCHAR(20)='',@XML_CUSTOMER XML='',@TBL NVARCHAR(MAX)=''


AS

BEGIN
SET NOCOUNT ON;
--CREATING TEMPORARY TABLE TO HOLD THE VALUES FROM THE EXCEL SHEET--

CREATE TABLE #CUSTOMER_TBL(
tbl_row_id int identity(1,1)primary key,
CUSTOMER_ID INT,
LINK_ID INT,
LOAN_NO BIGINT,
CUST_NAME VARCHAR(15),
OUTSTANDING FLOAT,
FINAL_POSITION FLOAT,
NATION_NAME VARCHAR(20),
COMPANY VARCHAR(15),
BANK_NAME VARCHAR(20),
PHONE_RES VARCHAR(15),
PHONE_OFFICE VARCHAR(15),
MOBILE VARCHAR(15),
FAX VARCHAR(15),
PASSPORT_NUMBER VARCHAR(50),
EMAIL VARCHAR(25),
DOB VARCHAR(30)
)

INSERT INTO #CUSTOMER_TBL(CUSTOMER_ID,LINK_ID,LOAN_NO,CUST_NAME,OUTSTANDING,FINAL_POSITION,NATION_NAME,COMPANY,BANK_NAME,PHONE_RES,PHONE_OFFICE,MOBILE,FAX,PASSPORT_NUMBER,EMAIL,DOB)
SELECT
CUST_TEMP.Item.value('@cust_id', 'INT'),
CUST_TEMP.Item.value('@link_id', 'INT'),
CUST_TEMP.Item.value('@loan_no', 'BIGINT'),
CUST_TEMP.Item.value('@cust_name', 'VARCHAR(15)'),
CUST_TEMP.Item.value('@out_standing', 'FLOAT'),
CUST_TEMP.Item.value('@final_pos', 'FLOAT'),
CUST_TEMP.Item.value('@nation_code', 'NVARCHAR(MAX)'),
CUST_TEMP.Item.value('@company', 'VARCHAR(15)'),
CUST_TEMP.Item.value('@bank_code', 'NVARCHAR(MAX)'),
CUST_TEMP.Item.value('@ph_res', 'VARCHAR(15)'),
CUST_TEMP.Item.value('@ph_office', 'VARCHAR(15)'),
CUST_TEMP.Item.value('@mobile', 'VARCHAR(15)'),
CUST_TEMP.Item.value('@fax', 'VARCHAR(15)'),
CUST_TEMP.Item.value('@pass_port', 'VARCHAR(50)'),
CUST_TEMP.Item.value('@email', 'VARCHAR(25)'),
CUST_TEMP.Item.value('@dob', 'VARCHAR(30)')

FROM @XML_CUSTOMER.nodes('/root/row') as CUST_TEMP(Item)
--SELECTING RELATION FROM DUMP_RELATION--


CREATE TABLE #TEMP(
TEMP_ROW_ID int identity(1, 1) primary key,
DUMP_FIELD VARCHAR(20))


INSERT INTO #TEMP(DUMP_FIELD)
SELECT DUMP_FIELD FROM DUMP_RELATION

--TRUNCATE TABLE #TEMP

--ITERATING USING THE WHILE LOOP--

DECLARE @RECORD INT
DECLARE @Ind INT
DECLARE @Index INT
DECLARE @RecordCnt INT
DECLARE @QUERY AS NVARCHAR(MAX)
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @RESULT NVARCHAR(MAX)
DECLARE @COUNT AS VARCHAR(20)

SELECT @Ind=1

SELECT @RECORD=COUNT(DUMP_FIELD) FROM #TEMP

WHILE(@Ind<=@RECORD)

BEGIN

SET @DUMP_FIELD=(SELECT DUMP_FIELD FROM #TEMP WHERE TEMP_ROW_ID=@Ind)
SET @REFER_FIELD=(SELECT REFER_FIELD FROM DUMP_RELATION WHERE DUMP_FIELD=@DUMP_FIELD)
SET @TABLE=(SELECT REFER_TABLE FROM DUMP_RELATION WHERE DUMP_FIELD=@DUMP_FIELD)
SET @FIELD=(SELECT RETURN_FIELD FROM DUMP_RELATION WHERE REFER_TABLE=@TABLE AND REFER_FIELD=@REFER_FIELD)
SET @TABLE_NAME=@TABLE


SELECT @RecordCnt = COUNT(*) FROM #CUSTOMER_TBL
SELECT @Index = 1
--ITERAING THROUGH THE COLUMNS IN THE TABLE--
WHILE(@Index<=@RecordCnt)

BEGIN

SET @DUMP_VALUE=(SELECT @DUMP_FIELD FROM #CUSTOMER_TBL WHERE tbl_row_id=@Index)


SET @RESULT=N'SELECT * FROM '+QUOTENAME(@TABLE_NAME)+' WHERE '+@REFER_FIELD+'='+@DUMP_VALUE+''


IF @RESULT IS NOT NULL

BEGIN
-- CREATE TABLE #TEMMMP(
-- FIELD VARCHAR(20))
--SET @sql=N'SELECT * INTO #TEMMMP FROM '+QUOTENAME(@TABLE_NAME)+'WHERE '+@REFER_FIELD+'='+@DUMP_VALUE+''
--EXEC (@sql)
--SELECT * FROM #TEMMMP
--DROP TABLE #TEMMMP

SET @QUERY=N'SELECT '+@FIELD+'FROM '+QUOTENAME(@TABLE_NAME)+' WHERE '+@REFER_FIELD+'='+@DUMP_VALUE+''

EXEC sp_executesql @COUNT= @QUERY
--SELECT @COUNT


--(SELECT @FIELD FROM QUOTENAME(@TABLE_NAME) WHERE @REFER_FIELD = @DUMP_VALUE )



--UPDATING WITH THE VALUE OBTAINED----


EXEC('UPDATE #CUSTOMER_TBL SET #CUSTOMER_TBL.'+@DUMP_FIELD+'= '+@COUNT +' WHERE tbl_row_id='+@Index)


END

-- ELSE

--BEGIN
--SET @QUERY=N'INSERT INTO '+QUOTENAME(@TABLE_NAME)+' VALUES '+(@DUMP_VALUE)
--EXEC(@QUERY)
-- END


SET @Index=@Index+1
END


SET @Ind=@Ind+1
END
--SELECT @COUNT
--SELECT * FROM #CUSTOMER_TBL

END


DROP TABLE #TEMP
DROP TABLE #CUSTOMER_TBL





Please replay anyone knows.....