We’re Baaaaaack!!!

If you have visited us within the past year, you may have noticed that we have been pretty quiet on our blog and may have wondered what happened.  Well…..we had some technical difficulties (our syntax highlighter plugin stopped working), then we got super busy, then came the holidays, then came COVID.  I know, Excuses…Excuses….Excuses.  Well, it really is the truth.

Anyway….we are turning over a new leaf here at SQLRX and have finally gotten a new syntax highlighter and have managed to find time to put together a couple of posts.  If you don’t mind, take a look at the new highlighter and give us some feedback.  I know the colors are not SSMS compliant but it is very readable and we like the functionality of it.

USE [AdventureWorks2016]
GO
/****** Object:  StoredProcedure [dbo].[uspGetEmployeeManagers]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspGetEmployeeManagers]
    @BusinessEntityID [int]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], 0 -- Get the initial Employee
        FROM [HumanResources].[Employee] e 
			INNER JOIN [Person].[Person] as p
			ON p.[BusinessEntityID] = e.[BusinessEntityID]
        WHERE e.[BusinessEntityID] = @BusinessEntityID
        UNION ALL
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
            INNER JOIN [EMP_cte]
            ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
            INNER JOIN [Person].[Person] p 
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
    )
    -- Join back to Employee to return the manager name 
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
        [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
    FROM [EMP_cte] 
        INNER JOIN [HumanResources].[Employee] e 
        ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
        INNER JOIN [Person].[Person] p 
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25) 
END;

We didn’t go away, we just did some…..

• Azure setup and management
• Always On
• SQL FCI’s
• SQL 2019
• Managed servers with THOUSANDS of databases
• New installations of SQL
• Updated our maintenance and monitoring tools
• Set up Encryption
• Performance tuned thousands of queries
• Created PowerBI reports
• Log shipping
• Replication

And that is just some of the SQL things we have been busy with.  We really have been busy.  Really.  Really.  Busy.  Whew!!  So, stay tuned because we will be posting more soon. 

One of us even rescued a kitten so here is your cute “kitteh” picture so you know that we care about kittens and we care about SQL. 

See you out here again VERY soon. 

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!