/***********************************
Author:			Tomas Westerlund, 861117-1490
Date:			2009-04-18
Course:			IV139V
Assignment:		Projekt2, Uppgift21
***********************************/
ALTER PROCEDURE tillaga_recept (@antal_port int, @recept_id int)
AS

DECLARE @del_variabel decimal(8,2)
DECLARE @receptnamn varchar(100)

IF NOT EXISTS	(SELECT * FROM dbo.Recept
				WHERE id = @recept_id)
	BEGIN
		PRINT 'Recept Saknas!'
	END

ELSE IF(@antal_port = 0)
	BEGIN
		PRINT 'Ska du verkligen räkna ut detta på 0 portioner?'
	END




ELSE IF  EXISTS		(SELECT * FROM dbo.Recept
				WHERE id = @recept_id)
	BEGIN

		SET @del_variabel = ((SELECT portioner FROM dbo.Recept WHERE id = @recept_id) / CAST(@antal_port as decimal(8,2)))

		CREATE TABLE #recept
		(ravaraID int,
		antal decimal(8,2))
			

		DECLARE @antal_cursor as decimal(8,2), @ravaraID int
			
		DECLARE receptomvandling CURSOR READ_ONLY
		FOR
		SELECT I.id, (CAST(I.Antal as decimal(8,2)) / @del_variabel)
		FROM dbo.Recept R
		INNER JOIN dbo.Ingrediens I ON I.recept_id = R.ID
		INNER JOIN dbo.Råvara RV ON RV.id = I.råvara_id
		WHERE R.id = @recept_id
		

		OPEN receptomvandling

		FETCH NEXT FROM receptomvandling
		INTO @ravaraID, @antal_cursor
		INSERT INTO #recept
		VALUES (@ravaraID, @antal_cursor)

		
		WHILE (@@FETCH_STATUS = 0)
		BEGIN

			FETCH NEXT FROM receptomvandling
			INTO @ravaraID, @antal_cursor
SELECT * FROM #recept		
PRINT 'Fetchstatus: ' + CAST(@@FETCH_STATUS as char(2))
			INSERT INTO #recept
			VALUES (@ravaraID, @antal_cursor)
SELECT * FROM #recept
PRINT 'Fetchstatus: ' + CAST(@@FETCH_STATUS as char(2))
		END

		CLOSE receptomvandling
		DEALLOCATE receptomvandling



	END

SELECT * FROM #recept

(1 row(s) affected)

(1 row(s) affected)
Fetchstatus: 0 

(1 row(s) affected)

(2 row(s) affected)
Fetchstatus: 0 

(2 row(s) affected)
Fetchstatus: 0 

(1 row(s) affected)

(3 row(s) affected)
Fetchstatus: 0 

(3 row(s) affected)
Fetchstatus: 0 

(1 row(s) affected)

(4 row(s) affected)
Fetchstatus: 0 

(4 row(s) affected)
Fetchstatus: -1

(1 row(s) affected)

(5 row(s) affected)
Fetchstatus: -1

(5 row(s) affected)
(1 row(s) affected)

declare crs for ...
open crs
fetch next from crs into ...
while @@fetch_status = 0
begin
     <do all your "stuff" here>

     fetch next from crs into ...
end

quote:

---

Originally posted by TG

The WHILE condition is only re-evaluated at the END statement.
I don't think you need a cursor for this. Why don't you simply INSERT all the rows into #receipt at once?

But if you use a cursor it is better to structure it like this:

declare crs for ...
open crs
fetch next from crs into ...
while @@fetch_status = 0
begin
     <do all your "stuff" here>

     fetch next from crs into ...
end

Be One with the Optimizer
TG

---

ELSE IF  EXISTS		(SELECT * FROM dbo.Recept
				WHERE id = @recept_id)
	BEGIN

		SET @del_variabel = ((SELECT portioner FROM dbo.Recept WHERE id = @recept_id) / CAST(@antal_port as decimal(8,2)))

		CREATE TABLE #recept
		(ravaraID int,
		antal decimal(8,2))
			

		INSERT INTO #recept
		SELECT I.id, (CAST(I.Antal as decimal(8,2)) / @del_variabel)
		FROM dbo.Recept R
		INNER JOIN dbo.Ingrediens I ON I.recept_id = R.ID
		INNER JOIN dbo.Råvara RV ON RV.id = I.råvara_id
		WHERE R.id = @recept_id
		

	END

ELSE IF @antal_port < 0 OR @antal_port > 19
	BEGIN
		PRINT 'Är du full eller?'
	END

ELSE IF @antal_port < 0 OR @antal_port > 19
	BEGIN
		PRINT 'Are you drunk?'
	END