Being Good With SQL, Using SQL Cursors

If you want to make any money programming in VB.NET then you need to be in the business of writing good SQL server code. VB, business programming and Databases all come together in the same package.  And so from time to time I will be giving out some tips about advanced SQL Server concepts.  I don't think of myself as a SQL Guru, but I do know a lot about it.  MS SQL is not the only database language you may need to learn, but the good news is that nearly all databases use the same T-SQL Language and so if you get good at one of them, it's generally pretty easy to use the others. Being good at designing table structure translates into EVERY database engine the same as well.


Today I wanted to talk for a moment about using SQL cursors, because they are very cool.  Have you ever wanted to do a loop inside a Stored procedure?  Well check out this code, you this will loop through all users that do not have a locker assigned (using a cool "NOT IN" SQL statement) and assign each of them the next available locker.

CREATE PROCEDURE [dbo].[spDEMO]

AS

BEGIN

    SET NOCOUNT ON;

DECLARE @idUser as int

DECLARE @idLocker as int

DECLARE UserLocker_cursor CURSOR FOR

        --Get all the users that don't have lockers

        SELECT idUser From tblUser where idUser NOT IN

            (

                SELECT idOwner from tblLocker where idOwner is not null

            )

        OPEN UserLocker_cursor

        FETCH NEXT FROM UserLocker_cursor

        INTO @idUser

        WHILE @@FETCH_STATUS = 0

        BEGIN

            SET ROWCOUNT 1 --Return only one record

            SELECT @idLocker=idLocker From tblLocker where idLocker is null

            SET ROWCOUNT 0 --Return all records (important to reset this setting!)

            --Now Assign a locker to each user           

            Update tblLocker set idOwner=@idUser Where idLocker = @idLocker

        FETCH NEXT FROM UserLocker_cursor

        INTO @idUser

        END

    CLOSE UserLocker_cursor

    DEALLOCATE UserLocker_cursor

END