looping stored procedure

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

AnnaJames77Asked:
Who is Participating?
 
x-menConnect With a Mentor IT super heroCommented:
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
 
kamindaCommented:
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
 
kamindaCommented:
sorry there is a typo in query

INSERT INTO tableB (column1, column2)
SELECT columna, columna * (columna - 1) FROM tableA
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AnnaJames77Author Commented:
no its not sequential so how can i
0
 
x-menIT super heroCommented:
use a CURSOR to iterate through the table, saving the previous value on a variable.
0
 
AnnaJames77Author Commented:
please can you explain it. iam new to this
0
 
x-menIT super heroCommented:
consider [lab] your tableA and @mult your tableB
0
 
anillucky31Connect With a Mentor Commented:
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

Open in new window

0
 
KrtyknmConnect With a Mentor Commented:
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

Open in new window

0
 
KrtyknmCommented:
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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.