Stored Procedures

seanmayhew · June 6th, 2004, 10:58 AM

I've copied and pasted all of the stored procedures into the Query Analyzer to run install them on my own DB rather than thePhile db but when I run the Analyzer I get this list of errors:

Code:

Server: Msg 207, Level 16, State 3, Procedure sp_Accounts_AddUserToRole, Line 9
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 1, Procedure sp_Accounts_AddUserToRole, Line 13
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 3, Procedure sp_Accounts_DeleteRole, Line 8
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 3, Procedure sp_Accounts_GetUserRoles, Line 6
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 1, Procedure sp_Accounts_GetUserRoles, Line 6
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 3, Procedure sp_Accounts_RemoveUserFromRole, Line 7
Invalid column name 'RoleID'.

Here are the SP queries.

Code:

CREATE PROCEDURE sp_Accounts_AddPermissionToRole
@RoleID int,
@PermissionID int
AS
    DECLARE @Count int

    SELECT @Count = Count(PermissionID) FROM Accounts_RolePermissions WHERE
        RoleID = @RoleID and PermissionID = @PermissionID

    IF @Count = 0
        INSERT INTO Accounts_RolePermissions(RoleID, PermissionID)
        VALUES(@RoleID, @PermissionID)

GO


CREATE PROCEDURE sp_Accounts_AddUserToRole
@UserID int,
@RoleID int
AS
    DECLARE @Count int

    SELECT @Count = Count(UserID) FROM Accounts_UserRoles WHERE
        RoleID = @RoleID AND UserID = @UserID

    IF @Count = 0
        INSERT INTO Accounts_UserRoles(UserID, RoleID)
        VALUES(@UserID, @RoleID)

GO


CREATE PROCEDURE sp_Accounts_ClearPermissionsFromRole
@RoleID int
AS
    DELETE Accounts_RolePermissions WHERE RoleID = @RoleID

GO


CREATE PROCEDURE sp_Accounts_CreateRole
@Description varchar(50)
AS
    INSERT INTO Accounts_Roles(Description) VALUES(@Description)
    RETURN @@IDENTITY

GO



CREATE PROCEDURE sp_Accounts_CreateUser
@EmailAddress varchar(255),
@Password binary(20),
@FirstName varchar(30),
@LastName varchar(30),
@Address1 varchar(80),
@Address2 varchar(80),
@City varchar(40),
@State varchar(2),
@ZipCode varchar(10),
@HomePhone varchar(14),
@Country varchar(50),
@UserID int output
AS
    INSERT INTO Accounts_Users(EmailAddress, Password, FirstName, LastName, Address1, Address2,
        City, State, ZipCode, HomePhone, Country)
    VALUES(@EmailAddress, @Password, @FirstName, @LastName, @Address1, @Address2,
        @City, @State, @ZipCode, @HomePhone, @Country)

    SET @UserID = @@IDENTITY

    RETURN 1

GO


CREATE PROCEDURE sp_Accounts_DeleteRole
@RoleID int
AS
    BEGIN TRANSACTION
        DELETE Accounts_RolePermissions WHERE RoleID = @RoleID
        DELETE Accounts_UserRoles WHERE RoleID = @RoleID
        DELETE Accounts_Roles WHERE RoleID = @RoleID
    COMMIT TRANSACTION

GO


CREATE PROCEDURE sp_Accounts_DeleteUser
@UserID int
AS
     DECLARE @MemberID int

     SELECT @MemberID = MemberId FROM Forums_Members WHERE UserId = @UserId

     IF @MemberID IS NOT NULL
     BEGIN
          DELETE Forums_Members WHERE UserId = @UserId
          DELETE Forums_Topics WHERE MemberId = @MemberId
          DELETE Forums_Replies WHERE MemberId =  @MemberId
     END

     DELETE Accounts_UserRoles WHERE UserId = @UserId
     DELETE ACcounts_Users WHERE UserId = @UserId
GO



CREATE PROCEDURE sp_Accounts_GetAllRoles
AS
    SELECT RoleID, Description FROM Accounts_Roles ORDER BY Description ASC

GO


CREATE PROCEDURE sp_Accounts_GetEffectivePermissionList
@UserID int
AS
   SELECT DISTINCT PermissionID FROM Accounts_RolePermissions WHERE RoleID IN
    (SELECT RoleID FROM Accounts_UserRoles WHERE UserID = @UserID)


GO

CREATE PROCEDURE sp_Accounts_GetPermissionCategories
AS
    SELECT * FROM Accounts_PermissionCategories
GO


CREATE PROCEDURE sp_Accounts_GetPermissionCategoryDetails
@CategoryID int
AS
    SELECT CategoryID, Description FROM Accounts_PermissionCategories WHERE CategoryID = @CategoryID

GO


CREATE PROCEDURE sp_Accounts_GetPermissionList
@RoleID int = NULL
AS
    IF @RoleID IS NULL
        SELECT PermissionID, Description, CategoryID FROM Accounts_Permissions ORDER BY Description
    ELSE
        SELECT ap.PermissionID, ap.Description, ap.CategoryID FROM Accounts_Permissions ap INNER JOIN
        Accounts_RolePermissions apr ON ap.PermissionID = apr.PermissionID WHERE
        apr.RoleID = @RoleID ORDER BY ap.Description

GO


CREATE PROCEDURE sp_Accounts_GetRoleDetails
@RoleID int
AS
    SELECT RoleID, Description FROM Accounts_Roles WHERE RoleID = @RoleID

GO

CREATE PROCEDURE sp_Accounts_GetStateList
AS
    SELECT * FROM Accounts_StateCodes ORDER BY StateCode ASC
GO


CREATE PROCEDURE sp_Accounts_GetUserDetails
@UserID int
AS
    SELECT * FROM Accounts_Users WHERE UserID = @UserID

GO


CREATE PROCEDURE sp_Accounts_GetUserDetailsByEmail
@EmailAddress varchar(255)
AS
    SELECT * FROM Accounts_Users WHERE EmailAddress = @EmailAddress

GO


CREATE PROCEDURE sp_Accounts_GetUserRoles
@UserID int
AS
    SELECT ur.RoleID, r.Description FROM Accounts_UserRoles ur
        INNER JOIN Accounts_Roles r ON ur.RoleID = r.RoleID  WHERE ur.UserID = @UserID

GO



CREATE PROCEDURE sp_Accounts_RemovePermissionFromRole
@RoleID int,
@PermissionID int
AS
    DELETE Accounts_RolePermissions WHERE RoleID = @RoleID and PermissionID = @PermissionID

GO


CREATE PROCEDURE sp_Accounts_RemoveUserFromRole
@UserID int,
@RoleID int
AS
    DELETE Accounts_UserRoles WHERE UserID = @UserID AND RoleID = @RoleID

GO



CREATE PROCEDURE sp_Accounts_TestPassword
@UserID int,
@EncryptedPassword binary(20)
AS
     DECLARE @TempID int
     SELECT @TempID = UserID FROM Accounts_Users WHERE UserID = @UserID AND
    Password = @EncryptedPassword

     IF @TempID IS NULL
    RETURN 0
     ELSE
    RETURN 1

GO


CREATE PROCEDURE sp_Accounts_UpdateRole
@RoleID int,
@Description varchar(50)
AS
    UPDATE Accounts_Roles SET Description = @Description WHERE RoleID = @RoleID

GO


CREATE PROCEDURE sp_Accounts_UpdateUser
@EmailAddress varchar(255),
@Password binary(20),
@FirstName varchar(30),
@LastName varchar(50),
@Address1 varchar(80),
@Address2 varchar(80),
@City varchar(40),
@State varchar(2),
@ZipCode varchar(10),
@HomePhone varchar(14),
@Country varchar(50),
@UserID int
AS
    UPDATE Accounts_Users SET
        EmailAddress = @EmailAddress,
        Password = @Password,
        FirstName = @FirstName,
        LastName = @LastName,
        Address1 = @Address1,
        Address2 = @Address2,
        City = @City,
        State = @State,
        ZipCode = @ZipCode,
        HomePhone = @HomePhone,
        Country = @Country
    WHERE UserID = @UserID

GO


CREATE PROCEDURE sp_Accounts_ValidateLogin
@EmailAddress varchar(255),
@EncryptedPassword binary(20)
AS
   DECLARE @UserID int

   SELECT @UserID = UserID FROM Accounts_Users WHERE EmailAddress = @EmailAddress
    AND Password = @EncryptedPassword

   IF @UserID != NULL   
    RETURN @UserID
   ELSE
    RETURN -1

GO

Im a SQL Server idiot I know I need a book but can someone help me understand this.

om_prakash · June 6th, 2004, 11:01 PM

Please Check if 'RoleID' column exists in Accounts_RolePermissions table.

Om Prakash

seanmayhew · June 7th, 2004, 07:49 PM

Yes there is a RoleID in the Accounts_RolePermissions table

englere · June 9th, 2004, 08:15 PM

What SQL Server user account was used to create the table? Maybe it's not the same SQL Server account as the one you used to create the procedures?

Just as C# and VB.NET use namespaces, SQL Server uses user accounts in a similar way to distinguish the differences between objects created by different users. To fully qualify the object name, you prefix it with the name of the SQL Server user.

Eric

seanmayhew · June 10th, 2004, 10:06 AM

Im sorry yes that was the problem my "RoleID" was mistyped as "RoldeID" Thanks!