Schlagwort-Archive: schleife

MSSQL while Loop

--Declare variable to store values returned by FETCH
DECLARE @f_id VARCHAR (10);
 
DECLARE myCursor CURSOR FOR
	SELECT     form_id
	FROM         dbo.str_formular
	WHERE     (bereich_id = 46) AND (workflow = 499)
		AND	form_id NOT IN (SELECT form_id
							FROM   dbo.str_formular_bereich
							WHERE     (bereichs_id = 46));
 
OPEN myCursor;
 
FETCH NEXT FROM myCursor
INTO @f_id;
 
WHILE @@FETCH_STATUS = 0
	BEGIN
		Print 'f_id: ' + @f_id
 
		INSERT INTO dbo.str_formular_bereich
		(form_id, bereichs_id)
		VALUES
		(@f_id, 46);
 
		FETCH NEXT FROM myCursor
		INTO @f_id;
 
	END
CLOSE myCursor;
DEALLOCATE myCursor;

Alle NICHT in der Lookup Tabelle (str_formular_baum) vorhandenen Einträge nachtragen.

--Declare variable to store values returned by FETCH
DECLARE @f_id VARCHAR (10);
DECLARE @f_name VARCHAR (100);
DECLARE myCursor CURSOR FOR
	SELECT     form_id, formname
	FROM         dbo.str_formular
	WHERE     (bereich_id = 46) AND (workflow = 499)
		AND	form_id NOT IN (SELECT form_id
							FROM   dbo.str_formular_baum
							WHERE     (parent_id = 623));
 
OPEN myCursor;
 
FETCH NEXT FROM myCursor
INTO @f_id, @f_name;
 
WHILE @@FETCH_STATUS = 0
	BEGIN
		Print 'f_id: ' + @f_id + 'f_name ' +@f_name
 
		INSERT INTO dbo.str_formular_baum
		(form_id, parent_id)
		VALUES
		(@f_id, 623);
 
		FETCH NEXT FROM myCursor
		INTO @f_id, @f_name;
 
	END
CLOSE myCursor;
DEALLOCATE myCursor;