how to select particular dataset from sql

Question

Hi,

I have a sql query that has two parts 1) Which calls a stored proc and populates the data in the temp table (using openXML function) 2) The other part of sql that joins with this table and the result set

Currently when i execute the stored proc it returns me the two result set. But it should return only me the second result set not the temp table result set.

My Visual Studio code by default selects the first result set whereas the required result set is second one. The sql is as follow :

@SQL = 'Create table #TempTable (YearEntry int, Quarter int) insert into #TempTable exec CreateTableFromXML @YearQuarterList '  + 
  ' Select * from ABCD inner join #TempTable T on T.YearEntry = A.Year '

It should return only all the columns from A table whereas it retuns

#TempTable and all the columns from A Table.

Can anybody please guide me how to get only the result set that returns all the columns from A table.

thanks

Answer 1

Instead of

' Select * from ABCD inner join #TempTable T on T.YearEntry = A.Year '

use

' Select ABCD.* from ABCD inner join #TempTable T on T.YearEntry = A.Year '

So you are specifying all of table ABCD, rather than everything from the join.

Answer 2

Is the first result set simply the (# row(s) affected) message that results from the INSERT statement? Try adding a SET NOCOUNT ON; as the first statement of your batch and see if that helps.