Solved

Problem with T-SQL loop in a stored procedure

Posted on 2002-04-26
3
407 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:ph0bia
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 6970821
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
 

Author Comment

by:ph0bia
ID: 6970833
Thanks a lot, I'll try that and let you know :-)
0
 

Author Comment

by:ph0bia
ID: 6970853
Thanks angelIII you're a star... it worked perfectly. I never knew that it did that.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now