cursor based solution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cursor based solution

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-08-12 : 13:18:00

Hi,

i have a cursor based solution which is in many of iterations do update of stock quantity against the table for stock quantity (table: ProductID, StockQuantity).

before the cursor goes into next iteration, it must update table for stock quantity, so that it knows how many products are still available in next step (for further calculation).

since cursors are relatively slow, is there any other work around that would for each row (or each step) have update of quantity against quantity table.

I was thinking of using CTE, but one can not use update statement within CTE. i can do it with while loop, but is there any other way?

thank you

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-12 : 13:20:43

quote:
I was thinking of using CTE, but one can not use update statement within CTE

Sure you can, I use UPDATEs in CTEs quite often. It has to be written a certain way of course.

As usual, you'll have to post your code, example data, and expected results for someone to offer a solution.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-08-13 : 05:55:58

Robvolk,

thank you for your answer.
Can you provide a tiny sample where you use UPDATEs in CTEs.

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-13 : 11:08:23

;with cte as (select rn, row_number() over (partition by customer order by orderdate) as rownum from mytable)
update cte set rn = rownum

N 56°04'39.26"
E 12°55'05.63"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-08-13 : 13:03:25

SwePeso,

thank you for your sample. using CTE like this i will update only at the end of recursion. but what i would need is to update table stockquantity each time for each iteration within recursion. i hope i'm making my clear :)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-13 : 13:57:00

Well, how about posting:

1) your table structure(s)
2) sample data
3) expected results
4) your current code

It's difficult to write a solution blindly.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-08-13 : 14:26:12

robvolk, you are right. i'm posting some code sample and instructions:


--DDL snippet

create table tbl_productStock
(productID int
,stockQ int
)

create table tbl_product
(productID int
,priority int
,billMaterial int
,QuantityNeeded int)


insert into tbl_productStock
select 100, 42 union all
select 101, 31 union all
select 102, 5 union all
select 103, 4 union all
select 1000, 0 union all
select 200, 6 union all
select 201, 11 union all
select 202, 15 union all
select 2000, 0 union all
select 300, 53 union all
select 301, 22 union all
select 3000, 0


insert into tbl_product
select 100, 1, 1000, 4 union all
select 101, 1, 1000, 3 union all
select 102, 1, 1000, 1 union all
select 103, 1, 1000, 2 union all
select 200, 2, 2000, 1 union all
select 201, 2, 2000, 3 union all
select 202, 2, 2000, 4 union all
select 101, 2, 2000, 1 union all
select 300, 2, 2000, 1 union all
select 300, 3, 3000, 10 union all
select 301, 3, 3000, 7 union all
select 100, 3, 3000, 2

i need to update quantity for all products, especially for products where
productID = 1000, 2000 or 3000.

for example, to construct productID: 1000, i need products 100, 101, 102 and 103
to construct productID: 2000, i need products 200,201,202,101 and 300. and so on.

Based on tbl_product.priority i will first construct productID 1000, then productID 2000 and so on.
Rules: i can construct product 1000, when i have all the necessary parts (product 100,101,102,103). if i can construct
5 products 1000, i will update stock quantity for product 1000 and diminish or substract all the product part quantity.

if i do not have enough products to build product 2000, i will go to next priority; product 3000.

i have cursor-based solution, but due to huge amount of data i would prefer set-based solution.

thank you

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-08-14 : 15:04:46

any ideas? :)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-14 : 16:20:01

Err, some patience? It's a weekend, most people aren't glued to their computer screens

--
Gail Shaw
SQL Server MVP

Celko
Starting Member

23 Posts

Posted - 2011-08-14 : 22:04:21

>> for example, to construct product_id: 1000, I need products 100, 101, 102 and 103 to construct product_id: 2000, I need products 200, 201, 202, 101 and 300 and so on. <<

Look up the term "Relational Division"; the dividend is your stockpile and the divisors are these "parts list"

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-08-15 : 03:48:47

Gila it's true :) it was weekend :)

anyways in between i've found three solutions:

- cursor based solution
- while loop based solution
- cte based solution

if anybody is interested :)

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-08-15 : 10:43:53

Base on the your example input, could you provide the expected result?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources