T-SQL LOOP

Pascal Ganaye

I find this code behave not the way it should. In this loop the vehicle registration persists from one contact to the next when no vehicle is found

FETCH NEXT FROM ContactsCursor INTO
@Contact_ContactID

WHILE (@@FETCH_STATUS = 0))
BEGIN
DECLARE @Vehicle_Registration AS VARCHAR(25)

SELECT  
    @Vehicle\_Registration     = V.Registration,
    FROM tblVehicle V (NOLOCK)
    WHERE V.ContactID = Contact\_ContactID

...

FETCH NEXT FROM ContactsCursor INTO 
	@Contact\_ContactID

END

I would have expected 1 - the DECLARE keyword to clear the value 2 - the SELECT should set to nul if the SELECT returns no row. The obvious fix is to write a @Vehicle_Registration. Is there any thing more elegant?

Jeremy Hutchinson

Apparently SQL doesn't recognize the scope of the cursor, so it isn't re-declaring the @Vehicle_Registration. I agree that's kind of odd behavior, and if it's not going to re-declare it I would at least like it to throw an error to let me know I'm trying to declare the variable twice and it's not going to do that. There's no more elegant way around the second issue where it doesn't update the @Vehicle_Registration when the select statement returns 0 rows. It's just always been that way, if no rows are returned there is nothing to assign to the variable. That's different than returning one row where V.Registration = null. I'm not sure what you're doing here, but you might want to consider what happens if a contact has more than one vehicle. You're only going to get the Registration for one of the vehicles, and there's no guaranty that it will be the same registration each time you run the code...

robocodeboy

A "Declare" statement doesn't initialize the declared value. And an assignment into a SELECT statement doesn't do anything if the result set is empty. So I would say it's working as expected. You should write something like this:

DECLARE @Vehicle_Registration AS VARCHAR(25);

FETCH NEXT FROM ContactsCursor INTO
@Contact_ContactID

WHILE (@@FETCH_STATUS = 0))
BEGIN
SET @Vehicle_Registration = NULL;

SELECT  
    @Vehicle\_Registration     = V.Registration,
    FROM tblVehicle V (NOLOCK)
    WHERE V.ContactID = Contact\_ContactID

IF (@Vehicle\_Registration IS NOT NULL)
BEGIN
	...
END

FETCH NEXT FROM ContactsCursor INTO 
	@Contact\_ContactID

END

See ya

PIEBALDconsult

This isn't the proper forum for this.

Pascal Ganaye

Really depend if I am after an answer or not. Here I know the answer. I am more complaining about the language, with the remote hope that perhaps someone will prove me wrong.

Pascal Ganaye

I won't tell for which insurance I work for then :-)

RyanEK

Pascal Ganaye wrote:

Is there any thing more elegant?

Yes... avoid cursors

Chris Maunder

This isn't that much different than if you declared the variable inside the loop in C++. If the inner select can't get any rows then there are no operations done, so of course the var is going to be left alone.

cheers, Chris Maunder The Code Project | Co-founder Microsoft C++ MVP

Simon_Whale

RyanEK wrote:

Yes... avoid cursors

for me that is WHERE possible which is 99% of the time

As barmey as a sack of badgers

AspDotNetDev

This is another way to do it (disclaimer: I didn't compile this code, so some of the syntax might not be quite correct):

-- Variables.
DECLARE @Vehicle_Registration AS VARCHAR(25)
-- Assume ContactsCursor and @Contact_ContactID are already declared.

-- Loop.
WHILE 0 = 0
BEGIN

-- Next item in cursor.
FETCH NEXT FROM ContactsCursor INTO
	@Contact\_ContactID
IF @@FETCH\_STATUS != 0
BEGIN
	BREAK
END

-- Get variable.
SELECT
	@Vehicle\_Registration =
		(
			SELECT TOP 1
				Registration
			FROM tblVehicle WITH (NOLOCK)
			WHERE
				ContactID = @Contact\_ContactID
		)

END

If you have to assign a bunch of values, you can perform a LEFT JOIN that ensures one row will always be returned:

-- Variables.
DECLARE @Vehicle_Registration AS VARCHAR(25)
-- Assume ContactsCursor and @Contact_ContactID are already declared.

-- Loop.
WHILE 0 = 0
BEGIN

-- Next item in cursor.
FETCH NEXT FROM ContactsCursor INTO
	@Contact\_ContactID
IF @@FETCH\_STATUS = 0
BEGIN
	BREAK
END

-- Get variable.
SELECT TOP 1
	@Vehicle\_Registration = Registration
	-- More variables assigned here.
FROM
	(
		SELECT 1 AS DummyValue
	) AS MinimumOneRow
LEFT JOIN tblVehicle WITH (NOLOCK)
	ON ContactID = @Contact\_ContactID

END

And if you think that's a neat trick, check out my article, JOIN Instead of Repeating a Subquery. :)

[Forum Guidelines]

PoweredByOtgc

This will fix your select issue when nothing is returned FETCH NEXT FROM ContactsCursor INTO @Contact_ContactID WHILE (@@FETCH_STATUS = 0)) BEGIN DECLARE @Vehicle_Registration AS VARCHAR(25) SELECT @Vehicle_Registration = coalesce(V.Registration,null), FROM tblVehicle V (NOLOCK) WHERE V.ContactID = @Contact_ContactID ... FETCH NEXT FROM ContactsCursor INTO @Contact_ContactID END

ScottM1

I had the exact same problem about a week ago except mine was with an integer that was keeping the previous row's value if the current row was null. You would think that all variables in the scope of the while loop would be null for each iteration.