Link to home
Start Free TrialLog in
Avatar of dba123
dba123

asked on

Stored Procedure Syntax Question

Here is my stored procedure below.  Do I need BEGIN AND END on these if statements?  If one ends, does it exit the whole Stored Procedure?

Also, it seems as though my second if statment is not running for IF @ResourceFoundID = "" or it's not finding any records or something for

SELECT @ResourceFoundID = ResourceID
FROM tbl_Resource
WHERE OwnerID = @PreviousOwnerID

I just want to be sure my syntax is correct with all these If statement and to see why the if statement I referenced above isn't deleting the record

----------------------------------------------------------

ALTER        PROCEDURE sp_Set_OwnerPermissions

      @ResourceID            int,
      @PreviousOwnerID      int,
      @NewOwnerID            int
AS


-- ## Determine if new owner already has a record in users table
-- ## if so, update their record to give them ResourceOwner permissions
-- ## otherwise, add them into the users table giving them ResourceOwner permissions

DECLARE @EmployeeID      int

SELECT @EmployeeID = u.EmployeeID
FROM tbl_Users u WHERE u.EmployeeID = @NewOwnerID
   IF @EmployeeID = ""
            INSERT INTO ERD.dbo.tbl_Users
            (EmployeeID, ResourceOwner, SiteAdmin)
            VALUES
            (@NewOwnerID, 1, 0)
    else if @EmployeeID <> ""

            UPDATE tbl_Users
            SET ResourceOwner = 1
            WHERE EmployeeID = @EmployeeID


--## Finally, if old owner isn't an owner for any other resources,
--## delete them in the users table if they are not a SiteAdmin user

DECLARE @ResourceFoundID       int

SELECT @ResourceFoundID = ResourceID
FROM tbl_Resource
WHERE OwnerID = @PreviousOwnerID

IF @ResourceFoundID = ""

            DELETE FROM tbl_Users
            WHERE EmployeeID = @PreviousOwnerID
            AND SiteAdmin <> 1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dba123
dba123

ASKER

>>>IF @ResourceFoundID = "" 
I want the If statement here to run if no record is found
Avatar of dba123

ASKER

Maybe I cna do If NOT EXISTS SELECT....Then

                      DELETE....
Avatar of dba123

ASKER

I think the other way, using the Declared @ResourceFoundID should work though.  If no records is found, then @ResoureFoundID should be null
Avatar of dba123

ASKER

excellent answers ac
>> If no records is found, then @ResoureFoundID should be null<<
You got it.