Problem with T-SQL loop in a stored procedure

Hi All,

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
BEGIN
     SET @i = @i + 1
     SELECT @ID2 = ID FROM Shipment WHERE Code = @OrderCode + '-' + CAST(@i AS nvarchar(2))
END

INSERT INTO Shipment VALUES (@OrderCode + '-' + CAST(@i AS nvarchar(2)), NULL, 0, @OrderID, 1)
GO

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.

TIA,
Ph0bia
ph0biaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Do this:

WHILE @ID2 is not null
BEGIN
    SET @i = @i + 1
    SET @ID2 = NULL
    SELECT @ID2 = ID FROM Shipment WHERE Code = @OrderCode + '-' + CAST(@i AS nvarchar(2))
END

The problem is that the select will not set @ID2 to NULL when no records are retrieved, but leave the value as is.

CHeers
0
 
ph0biaAuthor Commented:
Thanks a lot, I'll try that and let you know :-)
0
 
ph0biaAuthor Commented:
Thanks angelIII you're a star... it worked perfectly. I never knew that it did that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.