usp_updaterole.sql

来自「Beginning VB.NET DatabasesAll_Code.rar」· SQL 代码 · 共 39 行

CREATE PROCEDURE usp_UpdateRole
(
   @RoleID            UNIQUEIDENTIFIER,
   @RoleName          VARCHAR(50),
   @RoleDescription   TEXT,
   @Ranking           TINYINT
)
AS
-- Declare local variables
DECLARE @ID   UNIQUEIDENTIFIER

-- See if the ranking exists
SELECT @ID = RoleID FROM Roles WHERE Ranking = @Ranking
   BEGIN
   IF @ID IS NOT NULL
      BEGIN
      -- The ranking exists, now verify it doesn't belong to the 
      -- role you are updating
      IF @RoleID <> @ID
         BEGIN
         RAISERROR('Ranking already exists and cannot be duplicated.',18,1)
         RETURN
         END
      END
   END

-- Either the ranking does not exist or it belongs to the role being updated
UPDATE Roles
   Set RoleName = @RoleName, 
   RoleDescription = @RoleDescription, 
   Ranking = @Ranking, 
   LastUpdateDate = GETDATE()
   WHERE RoleID = @RoleID
IF @@ERROR <> 0 
   BEGIN
   RAISERROR('Update role failed.',18,1)
   RETURN
   END