Previous / Next PK ID Stored Procedure Bug

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Previous / Next PK ID Stored Procedure Bug

Author

Topic

digioz
Starting Member

2 Posts

Posted - 2010-01-09 : 12:00:10

Hello All,

I have a stored procedure that is suppose to do the following:

1. If the user wants to go Forward ( F ), it grabs the next PK ID and returns it.
2. If the user wants to go Backward ( B ), it grabs the previous PK ID and returns it.
3. If the user wants to go Forward and he is at the last record, it returns the very first record ID.
4. If the user wants to go Back and he is at the first record, it returns the very last record ID.

Here is the code I have written. For some reason item 3 and 4 return NULL always:


CREATE PROCEDURE spFlipPicture
    @id bigint,
    @step char(1)
AS
BEGIN

DECLARE @count bigint;

DECLARE @newid bigint;

SELECT @count = COUNT(*) FROM tblPictures;

IF (@step = 'F')

BEGIN

SET @newid = (SELECT TOP 1(picture_id) FROM tblPictures WHERE picture_id > @id);

END

ELSE IF (@step = 'B')

BEGIN

SET @newid = (SELECT TOP 1(picture_id) FROM tblPictures WHERE picture_id < @id ORDER BY picture_id DESC);

END

IF (@newid = NULL)

BEGIN

IF (@step = 'F')

BEGIN

SET @newid = (SELECT MIN(picture_id) FROM tblPictures);

END

ELSE

BEGIN

SET @newid = (SELECT MAX(picture_id) FROM tblPictures);

END

END

SELECT @newid AS ID;

END

The idea here is for the page to call this procedure in the format:

EXEC spFlipPicture 4,'F';

Does anyone have any idea why item 3 and 4 don't work on this code?

Thanks,
Pete

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-09 : 15:09:36

IF (@newid = NULL) doesn't work
IF @newid is null would work

No, you're never too old to Yak'n'Roll if you're too young to die.

digioz
Starting Member

2 Posts

Posted - 2010-01-09 : 15:34:54

That works. Thanks!

Pete

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-09 : 15:36:44

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-09 : 16:39:28

quote:
Originally posted by webfred

IF (@newid = NULL) doesn't work
IF @newid is null would work

To be fair, it depends on the current ANSI settings.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-09 : 18:15:18

Keep it simple and fast

DECLARE	@Sample TABLE
	(
		ID INT
	)

INSERT	@Sample
	(
		ID
	)
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	4 UNION ALL
SELECT	5 UNION ALL
SELECT	6

DECLARE	@ID INT = 2,
	@Direction CHAR(1) = 'F'

SELECT TOP(1)	ID
FROM		@Sample
ORDER BY	CASE 
			WHEN @Direction = 'F' AND ID > @ID THEN 0
			WHEN @Direction = 'B' AND ID < @ID THEN 0
			ELSE 1
		END,
		CASE @Direction
			WHEN 'F' THEN ID
			ELSE -ID
		END

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources