cursor in multi statements function

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

SQL Server 2005 Forums

Transact-SQL (2005)

cursor in multi statements function

Author

Topic

Gytenbryer
Starting Member

1 Post

Posted - 2007-10-05 : 12:29:58

Hello people

I have a question about t-SQL in SQL server. I'm using the database Northwind for exercising and I have a problem. I want to calculate the stock at this moment from all products.
To do this I’m using two tables called: Products (producten in dutch), transactions products in stock (voorraadtransacties in dutch).

The fields in this tables are:

Producten
Productnaam (name of the product)
Id (number of product)

Voorraadtransacties
[product-id] (number of product linked on Producten.Id)
[Type transactie] (type transaction, can be ‘ingekocht’ (bought) or ‘verkocht’ (sold)
Hoeveelheid (number of products that are bought or sold)

To calculate the stock of product I want to create a function that returns a table with te actual stoch for each product (productname and actual stock)

This is the code that I use.

if exists (
SELECT *
FROM sysobjects
WHERE name = 'fn_VoorraadPerProduct'
and type = 'FN')

DROP FUNCTION fn_VoorraadPerProduct
go

CREATE FUNCTION fn_VoorraadPerProduct()
RETURNS @Retour table
(Prnaam varchar(50),
Voorraden int - - actual stock
)
as
BEGIN

DECLARE @Rij int, @aantal int
DECLARE @prodnaam varchar(50)
DECLARE @hoev int
DECLARE @voorraad int
DECLARE @type smallint

DECLARE cur1 CURSOR for
SELECT p.Productnaam, v.[Type transactie], v.Hoeveelheid
FROM Producten p, Voorraadtransacties v
WHERE p.id = v.[Product-id]
GROUP BY P.Productnaam, v.[Type transactie], v.Hoeveelheid

OPEN cur1

SET @rij = 1
SET @aantal = @@Cursor_rows
WHILE @rij < @aantal
BEGIN
FETCH NEXT FROM cur1 INTO @prodnaam, @type, @hoev
If @type = 1
set @voorraad = @voorraad + @hoev
if @type = 2
set @voorraad = @voorraad - @hoev
set @rij = @rij + 1
END
close cur1

INSERT INTO @Retour
SELECT p.Productnaam, @voorraad
FROM Producten p, Voorraadtransacties v
WHERE p.id = v.[Product-id]
return
END
go

my question is how do i get a the calculated stock in a column of the return table???

who has the solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources