Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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
0
dba123
Asked:
dba123
  • 4
  • 2
1 Solution
 
Anthony PerkinsCommented:
>>Do I need BEGIN AND END on these if statements? <<
Strictly speaking you only need Begin and End when there is more than one statement.  Therefore, in your case it is not necessary.  Though it is always best to use them for clarity.

>>If one ends, does it exit the whole Stored Procedure?<<
No.

>>Also, it seems as though my second if statment is not running for IF @ResourceFoundID = "" <<
You are right.  Since @ResourceFoundID is declared as integer the line:
IF @ResourceFoundID = "" 
Will never be true.

Perhaps you mean:
IF @ResourceFoundID Is Null

Or:
IF @ResourceFoundID = 0
0
 
dba123Author Commented:
>>>IF @ResourceFoundID = "" 
I want the If statement here to run if no record is found
0
 
dba123Author Commented:
Maybe I cna do If NOT EXISTS SELECT....Then

                      DELETE....
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
dba123Author Commented:
I think the other way, using the Declared @ResourceFoundID should work though.  If no records is found, then @ResoureFoundID should be null
0
 
dba123Author Commented:
excellent answers ac
0
 
Anthony PerkinsCommented:
>> If no records is found, then @ResoureFoundID should be null<<
You got it.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now