-1

I have a 1 stored procedure in which i have used following query:

DECLARE @product_id int
select @product_id=id from rpidata where collection_id = 354
select @product_id

And it gives me only 1 last record. Now when i hit below query then it gives me 5 records.

 select id,ngj_id,tul_price from rpidata where collection_id = 354

Now i want to get all these records and do loop to calculate other prices. Example:

 DECLARE @product_id int
    for loop start //To traverse all 5 records
       select @product_id=id from rpidata where collection_id = 354
       select @product_id
       //My custom calculation for other fields.
   for loop end

How can i do that ?

siddharth
  • 256
  • 4
  • 14
  • 1
    Doing calculations row-by-row is usually a bad idea in SQL. Post what you really need to do so we can help you with a set-based solution. – Luis Cazares Mar 28 '19 at 15:17
  • There are no major calculation. i have to just add static number in that id and add do check with other table that if there any data exists or not. – siddharth Mar 28 '19 at 15:50
  • Post the complete problem to give you a complete solution. – Luis Cazares Mar 28 '19 at 15:57

2 Answers2

1

You need table variable :

declare @producttable table (
        product_id int,
        ngj_id <required-type>,
        tul_pric <required-type>
)
insert into @producttable (product_id)
    select id, ngj_id, tul_pric
    from rpidata 
    where collection_id = 354;

select pt.*
from @producttable pt;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
1

Again. Loops is not the best idea to go about in SQL. Go for it, when you have no way out. Adding to @Yogesh's answer:

declare @producttable table (
    product_id int,
    ngj_id <required-type>,
    tul_pric <required-type>
)

declare @count int=0
declare @num_of_rows int=0
declare @row_id = -1

insert into @producttable (product_id, ngj_id, tul_pric)
  select id, ngj_id, tul_pric
  from rpidata 
  where collection_id = 354

select @num_of_rows = count(*) from @producttable

while(@count <= @num_of_rows)
begin 

  select @row_id = product_id from @producttable ORDER BY product_id LIMIT 1
  //rest of your code


  set @count=@count+1
end
sabhari karthik
  • 1,361
  • 6
  • 17