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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Maybe I cna do If NOT EXISTS SELECT....Then
DELETE....
DELETE....
ASKER
I think the other way, using the Declared @ResourceFoundID should work though. If no records is found, then @ResoureFoundID should be null
ASKER
excellent answers ac
>> If no records is found, then @ResoureFoundID should be null<<
You got it.
You got it.
ASKER
I want the If statement here to run if no record is found