CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SterlinkArcher
SterlinkArcher

Reputation: 731

Adding column to a resultset in stored procedure

I'm working on SP, I want to add a column to a resultset. Normally this would not be a proble, but here I'm using an Exec to fill one temp-table. To that temp-table I want to add one column.

Some prestuff that puts data in one of the temp-tables with some conditions

declare @RowCount int

set @RowCount = 1
create table #Temp_HS (row int IDENTITY (1, 1) NOT NULL, h varchar(30))
Create table #tmpS (K varchar(100), 
U varchar(100), Counter int, H varchar(100))


--Puts data in one temp_table with employees
insert into #Temp_HS (h)
select Login from database.dbo.Users
where Old <> 1
and TC in ('A_1', 'A_2')
and Login not in ('Steve', 'Peter', 'Gabs')

--Declaring my counter here, it sets the MaxRow which is 19 in this case
declare @Counter int 
set @Counter = (select Max(row) from #Temp_HS)

select * from #Temp_HS
-- Looping, That my RowCount must be less or Equal to Counter which is 19.
while @RowCount <= @Counter
begin

Set User which was originally from the Database Login which is declared as H in the temp table.

declare @user varchar(30)
select @user = h from #Temp_HS where row = @RowCount

Here comes the tricky part, this is the Stored procedure that inserts 3 columns into a temp table, here I want to add one colum which in this case is h from Temp_HS to the resultset. 

INSERT INTO #tmpS
EXEC  Database.dbo.getListCount @user,
param, 
param, 
param, 
'something',
param 



set  @RowCount = @RowCount +1
end

drop table #Temp_HS

If you need any further information just ask! :)

Basically I want to add one more column to the results of my Exec SP that inserts the result into a temp_table

Upvotes: 1

Views: 5438

Answers (2)

StuartLC
StuartLC

Reputation: 107247

INSERT INTO .. EXEC requires that the table you are inserting into already exists, e.g.

-- Given this preexisting proc
CREATE PROC dbo.getListCount @user INT, -- other params
AS
   SELECT @User as Col1, 
          'SomeVarChar' as Col2
   FROM [SomeTable];


-- In your code, create the temp table to hold the data
CREATE TABLE #tmpS
(
   Col1 INT,
   Col2 NVARCHAR(100),
   NewColumnH VARCHAR(30) -- Add the additional column up front
   -- etc.
);

This is called as

INSERT INTO #tmpS(Col1, Col2)
   EXEC dbo.getListCount, @User;

If you then need to do do further processing on your temp table, do this after the PROC call:

UPDATE ts
   SET NewColumnH = t.h 
   FROM #tmpS ts INNER JOIN #Temp_HS th on th.row = @RowCount;

Upvotes: 1

SterlinkArcher
SterlinkArcher

Reputation: 731

Actually inner join doesnt work as desireed on temp tables that is why I used this solution. Since I already had @User in a variable I choose to do this update instead. 

UPDATE ts
SET NewColumnH = @User 
FROM #tmpS ts
where ts.Column is null

Upvotes: 0

Related Questions