2007-05-27

T-SQL - Recursion Levels / Selecting Tables Managers from an Employee Table

You've probably worked with recursive relationships at one point or another, and sometimes you need to compile advanced recursive datasets based on this. Perfect example of this is the age-old Employee / Manager table. See:
Employee
EmployeeID
EmployeeName
ManagerID
In this example, ManagerID is a foreign key to the EmployeeID, so that they child record is another employee in the same table.

Here's the code, sans explanation. Details of the With/CTE can be found on MSDN at http://msdn2.microsoft.com/en-us/library/ms175972.aspx. If I get some time before I forgot, I'll come back and explain this.
CREATE PROCEDURE [GetEmployeeManagerHeirarchy]
@EmployeeID INT
AS
BEGIN

WITH [EMP_cte]
(
[EmployeeID]
, [ManagerID]
, [EmployeeName]
, [RecursionLevel]
) -- CTE name and columns
AS
(
SELECT
e.[EmployeeID]
, e.[ManagerID]
, e.[EmployeeName]
, 0 -- Get the initial Employee
FROM [Employee] e

WHERE e.[EmployeeID] = @EmployeeID

UNION ALL

SELECT
e.[EmployeeID]
, e.[ManagerID]
, e.[EmployeeName]
, [RecursionLevel] + 1 -- Join recursive member
to anchor
FROM [Employee] e

INNER JOIN [EMP_cte]
ON e.[EmployeeID] = [EMP_cte].[ManagerID]
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel]
, [EMP_cte].[EmployeeID]
, [EMP_cte].[EmployeeName]
, [EMP_cte].[ManagerID]
, c.[EmployeeName] AS 'ManagerName' -- Outer select from the CTE
FROM [EMP_cte]

INNER JOIN [Employee] e
ON [EMP_cte].[ManagerID] = e.[EmployeeID]

ORDER BY
[RecursionLevel]
, [ManagerID]
, [EmployeeID]
OPTION (MAXRECURSION 25)

END;

2007-05-13

CSS - Two Column Layout + Header, Footer

No frills, and no guff. Here is.

Html



<div id="PageWrapper">
 <div id="Header">...</div>
 <div id="Navigation">...</div>
 <div id="Content">...</div>
 <div id="Footer">...</div>
</div>


CSS



#PageWrapper
{
 width:980px;
 margin:0 auto 0 auto;
}

#Header
{
 margin:0;
 text-align:left;
}

#Navigation
{
 float:left;
 width:185px;
 padding:0;
 margin:0;
}

#Content
{
 padding-top:0px;
 margin:0 0 0 200px;
 width:700px;
}

#Footer
{
 clear:both;
 text-align:left;
 margin:0;
}

Here's what you need to know!

• If you want a wider menu, you have to increase the left margin on #Content and the width #Navigation
• If you want to change the page width change the #PageWrapper.
• #PageWrapper's auto margin's center the page nicely. Change if you'd like.

2007-05-12

T-SQL - Almost Polymorphic Stored Procedures

One of the bigger complaints of Stored Procedures in T-SQL is their inability to attend to many different functional needs at once. It's often standard practice in many organizations to write several similar scripts that only vary in the search criteria--the same types of data will be returned regardless.

Here's one way to surmise this limitation.

We're going to utilize these features built into T-SQL
• IsNull(a, b) - IsNull is a system function built into T-SQL that will return value a, if it's a is not null. If a is null, return b.
• Default Parameter Values - By defaulting all search criteria to null, we should be able to fully utilize IsNull(a, b)

So here's the code...



CREATE PROCEDURE [GetSomeData]
@Field1 INT = NULL
, @Field2 INT = NULL
, @Field3 VARCHAR(10) = NULL
AS
BEGIN

SELECT
[Field1]
, [Field2]
, [Field3]
FROM [TableName]
WHERE [Field1] = IsNull(@Field1, [Field1])
AND [Field2] = IsNull(@Field2, [Field2])
AND [Field3] = IsNull(@Field3, [Field3])

END


Basically, what we have here...

We've typed out our stored procedure in full, with all of our search criteria, but instead of leaving the criteria as Field1 = @Field1, we've opted to use the identity concept of the underlying data. Like this... Field1 = IsNull(@Field1, Field1). Basically, if the consumer doesn't pass it a value for @Field1, this Sproc will return all values where Field1 = Field1, which should be every record.

Finally, how it's used:



-- Returns All Results from SPROC
EXECUTE [GetSomeData]

-- Returns Results WHERE @Field1 is 1
EXECUTE [GetSomeData] @Field1 = 1

-- Returns Results WHERE @Field2 is 2
EXECUTE [GetSomeData] @Field2 = 2

-- Returns Results WHERE @Field3 is 'text'
EXECUTE [GetSomeData] @Field3 = 'text'

-- Returns Results WHERE Field1 is 1 and @Field3 is 'text'
EXECUTE [GetSomeData] @Field1 = 1, @Field3 = 'text'

-- Returns Results WHERE @Field is 2 and @Field3 is 'text'
EXECUTE [GetSomeData] @Field2 = 2, @Field3 = 'text'




Hope the buds your frustrations!

Welcome to uberCode!

Welcome to uberCode!

We're not bloggers. We code.