Solved

# looping stored procedure

Posted on 2011-05-10
243 Views
hi i have a table A having records 1 to 10. i need to create a stored procedure that loops through each of the recs of A and multiply with previous row value and inserts to a new table B
say table A
1
2
3
4
5
then after running procedure table b should be with val

1    1
2    2
3    6
4   12

ie eg: 3 * 2 =6 2 is the val of prev rec

0
Question by:AnnaJames77

LVL 9

Expert Comment

If the values in sequence order without gaps you can use simple query like this

INSERT INOT tableB (column1, column2)
SELECT columna, columna * (columna - 1) from tableA
0

LVL 9

Expert Comment

sorry there is a typo in query

INSERT INTO tableB (column1, column2)
SELECT columna, columna * (columna - 1) FROM tableA
0

Author Comment

no its not sequential so how can i
0

LVL 18

Expert Comment

use a CURSOR to iterate through the table, saving the previous value on a variable.
0

Author Comment

please can you explain it. iam new to this
0

LVL 18

Accepted Solution

declare @mult table (C1 int, C2 int)
declare @val1 int, @val2 int
declare iterate cursor for select x from lab
open iterate
Set @val1 = Null
fetch next from iterate into @val2
WHILE @@FETCH_STATUS = 0
begin
If @val1 is null
insert into @mult VALUES (@val2,@val2)
Else
insert into @mult VALUES (@val2,@val2*@val1)

Set @val1 = @val2
fetch next from iterate into @val2
end
select * from @mult
0

LVL 18

Expert Comment

0

LVL 9

Assisted Solution

I have one question on this. How you will determine that what is previous row for current record.
Firstly you need to sort the records on the basis of some column, then  your rows will be in some order.

I am assuming that you have sorted rows on some column name.

IN this example i am sorting ROWS ON COLUMN ID. You also have TO sort ON SOME COLUMN NAME

IF you dont want TO sort THEN just remove ORDER BY IN my example. I have attached my code sample
``````DECLARE @Id int
DECLARE @Value INT
DECLARE @PreviousValue INT
DECLARE @NewValue int

SET @PreviousValue = 1

DECLARE somecursor CURSOR FOR SELECT Id, Value FROM TableA ORDER BY id asc
OPEN somecursor
FETCH NEXT FROM somecursor INTO @Id, @Value

WHILE @@FETCH_STATUS = 0
BEGIN

SET @NewValue = @Value * @PreviousValue

INSERT INTO TableB (Id, Value)
VALUES(@Id, @NewValue)

SET @PreviousValue = @Value

FETCH NEXT FROM somecursor INTO @Id, @Value
END

CLOSE somecursor
DEALLOCATE somecursor

SELECT * FROM TableB
``````
0

LVL 3

Assisted Solution

I have just made it thru common table expression (CTE) , since it will be a set based approach the execution will be very fast.
``````WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY id) [RowNum],
[id]
FROM dbo.tblA

)

INSERT INTO dbo.[tblB] (id,Cartesianid)
SELECT
[id],
[id]*[id] [Cartesianid]
FROM CTE AS A
WHERE [RowNum] = 1
UNION ALL
SELECT
A.[id]
,A.[id] * B.[id] [Cartesianid]
FROM dbo.tblA AS A
INNER JOIN  dbo.tblA AS B
ON A.[id] = B.[id] + 1
``````
0

LVL 3

Expert Comment

Sorry, I have attached the wrong one, the below query will bring the exact values as you expect
``````WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY id) [RowNum],
[id]
FROM dbo.tblA

)
INSERT INTO dbo.[tblB] (id,Cartesianid)
SELECT
[id],
[id]*[id] [Cartesianid]
FROM CTE AS A
WHERE [RowNum] = 1
UNION ALL
SELECT
A.[id]
,A.[id] * B.[id] [Cartesianid]
FROM CTE AS A
INNER JOIN CTE AS B
ON A.[RowNum] = B.[RowNum] + 1
``````
0

## Featured Post

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.