Microsoft® SQL Server® 2012 Bible

864

Part VI: Securing Your SQL Server (Jose)

s.SecurityLevel

FROM dbo.Security AS s

INNER JOIN Person.Person AS p

ON s.PersonID = p.BusinessEntityID

INNER JOIN Person.Address AS a

ON s.AddressID = a.AddressID

WHERE (a.AddressID = @AddressCode

OR @AddressCode IS NULL)

AND (p.BusinessEntityID = @PersonCode

OR @PersonCode IS NULL);

Adding or altering rows in the Security table, which serves as a junction between person

and location, in keeping with the theme of server-side code, the pSecurity_Assign

stored procedure assigns a security level to the person/address combination. There’s

nothing new about this procedure. It accepts a person code and address code, and then

performs the insert:

CREATE PROCEDURE pSecurity_Assign

@PersonCode VARCHAR(15),

@AddressCode VARCHAR(15),

@SecurityLevel INT

AS

SET NOCOUNT ON;

DECLARE

@PersonID int,

@AddressID int;

-- Get PersonID

SELECT @PersonID = BusinessEntityID

FROM Person.Person

WHERE BusinessEntityID = @PersonCode;

IF @@ERROR <> 0 RETURN -100

IF @PersonID IS NULL

BEGIN;

RAISERROR

('Person: ''%s'' not found', 15,1,@PersonCode);

RETURN -100;

END;

-- Get AddressID

SELECT @AddressID = AddressID

FROM Person.Address

WHERE AddressID = @AddressCode;

IF @@ERROR <> 0 RETURN -100;

IF @AddressID IS NULL

BEGIN;

RAISERROR

c35.indd 864c35.indd 864 7/31/2012 9:59:40 AM7/31/2012 9:59:40 AM

http://www.it-ebooks.info