This problem has really been bugging me and I hope someone can help. I have written a stored procedure for SQL Server 2000 in T-SQL that searches through codes in sequence until it finds an empty code and then inserts the new record.
I am using a WHILE @ IS NOT NULL loop but the code doesn't jump out of the loop when the variable is NULL
I will post the important bits of my code below.
CREATE PROCEDURE ShipItems (@OrderID as int) AS
DECLARE @OrderCode as nvarchar(14), @i as int, @ID2 as int
SELECT @OrderCode = Code FROM [Order] WHERE ID = @OrderID
SET @i = 1
SELECT @ID2 = ID FROM Shipment WHERE Code = @OrderCode + '-1'
WHILE @ID2 is not null
SET @i = @i + 1
SELECT @ID2 = ID FROM Shipment WHERE Code = @OrderCode + '-' + CAST(@i AS nvarchar(2))
INSERT INTO Shipment VALUES (@OrderCode + '-' + CAST(@i AS nvarchar(2)), NULL, 0, @OrderID, 1)
Any help with this would be much appreciated as I don't understand why it isn't working 'cos I have used loops like this before without any problems.