Please help....
i am posting the follwing stored procedure. when i execute it it give
error. I can't seem to place the error....

CREATE PROC database_login_delete
    @dbMain        varchar(80)    /* Input variable holding name of database to be
decommisioned */
AS
SET NOCOUNT ON
DECLARE @SQL        varchar(999),    /* Variable holding the temp queries to be
executed */
    @dbName        varchar(80),    /* Variable for the current database name in the
cursor*/
        @loginName    varchar(80)    /* Variable for the current login name
in the cursor*/

PRINT 'We are starting the procedure'
/* Declare cursor to loop through all the databases except the system
databases and the database to be decommisioned*/
DECLARE dbCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT  catalog_name
FROM    information_schema.schemata
WHERE   catalog_name NOT IN ( @dbMain , 'audit', 'efo_spt', 'master',
'model', 'msdb', 'rau', 'sdtime', 'tempdb', 'terrywhite', 'training',
'virgin' )
PRINT 'dbCursor is initialized'

/* Declare cursor to loop through all the logins */
DECLARE loginCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT name FROM sysxlogins WHERE name NOT LIKE 'NULL'
PRINT 'loginCursor is initialized'

/* Running a while loop till all the logins are covered */
OPEN loginCursor
FETCH NEXT FROM loginCursor INTO @loginName
PRINT 'Checking for ' + @loginName
WHILE (@@FETCH_STATUS = 0)
BEGIN
    Declare    @deleteFlag int
    Declare @flagValue int
    SET @deleteFlag = 0
    /* Checking whether the user exists in the database to be
decommisioned */
    EXEC sp_executesql N'IF NOT EXISTS (SELECT sid FROM @dbMain..sysusers
WHERE name LIKE @loginName)
    BEGIN
        SET @deleteFlag = @flagValue
    END', N'@flagValue int OUTPUT, @deleteFlag int OUTPUT', @flagValue
OUTPUT, @deleteFlag OUTPUT

    IF (@deleteFlag <> 2)
    BEGIN
        /* Running a while loop till all the databases are covered */
        OPEN dbCursor
        FETCH NEXT FROM dbCursor INTO @dbName
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXEC sp_executesql N'IF NOT EXISTS (SELECT sid FROM
@dbName..sysusers WHERE name LIKE @loginName)
            BEGIN
                SET @deleteFlag = @flagValue
            END', N'@flagValue int OUTPUT, @deleteFlag int OUTPUT', @flagValue
OUTPUT, @deleteFlag OUTPUT
            /* if the login is found in the database then break out of the inner
loop */
            IF (@deleteFlag = 1)
                BREAK
            ELSE
                CONTINUE

            FETCH NEXT FROM dbCursor INTO @dbName
        END
        CLOSE dbCursor
        DEALLOCATE dbCursor
    END
    IF (@deleteFlag = 0)
    BEGIN
        /* delete user from the database */
        PRINT @loginName + ' is to be deleted from the database'
    END
    FETCH NEXT FROM loginCursor INTO @loginName
END
CLOSE loginCursor
DEALLOCATE loginCursor
/*END OF STORED PROCEDURE*/


in the Query analyser i type  "EXEC database_login_delete 'Compaq' "
The part which is giving this error runs without a problem whn i run it
idependently...

Thanx in advance

Nikhil Bhatia (bhatia.nik***@gmail.com) writes:
1) @dbname is not declared.
2) You cannot have a variable in that position anyway.

Ever growing transaction log, using simple recovery?
Help with efficiently storing a variety nested queries
Max Row Date
Simple trigger question
The pains of XML programming