How to store results from a query while in a stored procedure

Question

Say I have this great query in my stored procedure.

Select * from Temp

How would I store the results of this in the same stored procedure since in the next line I want to go through it in a loop(I don't know how to do this yet either) and do stuff to it.

I found something like this

 DECLARE total_count INT DEFAULT 0
 SET total_count = 10;

but it seems like that does not work.

Msg 156, Level 15, State 1, Procedure csp_test, Line 3
Incorrect syntax near the keyword 'DECLARE'.
Msg 155, Level 15, State 2, Procedure csp_test, Line 3
'INT' is not a recognized CURSOR option.

Edit

Ok this is what I go so far. I have no clue what I am doing so I don't know if this is remotely right.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[csp_test]  
AS
BEGIN

declare @temp2 table (
    idx int identity(1,1),
    field varchar(max))

insert into @temp2 (field)
Select * from temp


END

So What I think this is doing is it makes some table variable then inserts all my results from temp table into this temp2 table variable. Then I loop through them or something like that?

I don't if what I have is so far right. I then found this and not sure if this would be the next step

declare @counter int

set @counter = 1

while @counter < (select max(idx) from @temp)
begin
    -- do what you want with the rows here
    set @counter = @counter + 1
end

Temp Table script

USE [test]
GO
/****** Object:  Table [dbo].[temp]    Script Date: 07/06/2010 19:20:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[temp](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [temp] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_temp] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

score 2 · Answer 1 · edited May 23 '17 at 11:48

2

--Variable table @table
declare @table as Table (int i, ...)

insert into @table
Select * from Temp

or

--Temporary table #temp
create table #temp (int i, ...)

insert into #table
Select * from Temp

--Use it

--Finally
drop table #temp

What you found should be:

DECLARE @total_count INT DEFAULT 0
SET @total_count = 10;

Variables starts with @

For info about differences I've found this article and a stackoverflow question.

edited May 23 '17 at 11:48

Community

1
1

answered Jul 07 '10 at 02:01

user347594

1,256
7
11

as john write : you need to declare table and save data in int you can also check this http://odetocode.com/code/365.aspx – Amr Badawy Jul 07 '10 at 02:07
So I have to make some internal table then get rid of it? Your first way you don't drop it. Does it get dropped automatically? How do I loop through the results if there in some other table? – chobo2 Jul 07 '10 at 02:07
Table variables don't need to be dropped or deleted. Their resources are clean automatically if created in the same stored procedure (in sql2008 they can be passed as arguments, and I don't know how it is handled the resources here). And to loop declare a CURSOR like this. DECLARE cursor_name CURSOR FOR select_statement_using_temporary_table --Complete reference here: http://technet.microsoft.com/es-es/library/ms180169.aspx – user347594 Jul 07 '10 at 02:11
hmm ok how is cursor different then while loop? – chobo2 Jul 07 '10 at 02:25

score 0 · Accepted Answer · edited Jul 06 '16 at 13:56

This is a handy template for creating a temp table, filling with data, then cursor through the data for some reason

-- create temp table
CREATE TABLE #tmp (field1 int, field2 varchar(10)) ON [PRIMARY]

-- populate temp table
insert into #tmp (field1, field2)

select  something1, something2
from    someTable

-- variables for cursor through temp table
declare @field1 int
declare @field2 varchar(10)

-- open cursor
declare myCursor Cursor for select field1, field2 from #tmp
open myCursor

-- get 1st row of data
fetch next from myCursor into @field1, @field2

-- loop through the data
while @@fetch_status = 0 begin
      -- do sumthin.. data is in @field1 and @field2
      -- get next row
      fetch next from myCursor into @field1, @field2
end

-- get rid of cursor
close myCursor
deallocate myCursor

-- drop temp table
drop table #tmp

How to store results from a query while in a stored procedure

2 Answers2