Recursive Stored Procedure query

I stumbled upon a great stored procedure for Recursive Queries (here), but I wanted to recreate it for AdventureWorks.

Note: This one only works on the previous AdventureWorks database (not 2008R2, the Employee table has changed). Soon I’ll be posting a modified version of this based upon the AdventureWorks2008R2 database.

Basicly the Employee-table is self referencing table with levels (also known as ‘Tree Walking‘) has an EmployeeId and ManagerId I’m going to use.

The code:

How to execute:

And ofcourse, the results are shown here:

A really nice thing about this SP is that it uses a parameter Root, which can be used to ‘climb’ the tree. For example, if I set @Root to be 12:

Want the SQL sourcecode? Click here


SQL Server 2008 query hint: Optimize for Unknown

A new query hint for SQL Server 2008 is: Optimize for Unknown

The query hint ‘Option (Optimize for (@Par = value))’ can also be used for SQL Server 2005.

‘Optimize for Unknown instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.’ Source: Query Hints

Basicly the query optimizer will ignore the parameter value during query optimization and will use the same query execution plan for each parameter value.

‘Optimize for Unknown’ is the new problem solving solution for ‘Parameter Sniffing’
(A good example of Parameter Sniffing)

Note: ‘If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.’

‘All’-entity within a Multi-value parameter

In addition to my previous post, an ‘All’-entity is very handy when it comes to some reports.
In my example, I’ll continue using the Sales example from my previous post.

Normally when you have a parameter with just one value, it’s easy: just use a NULL-value (click).
But when it comes to Multi-value parameters, it’s a bit more tricky.

I’ve added an ‘(All)’-entity to my selection of the SalesTerritory table:

In combination with my MultiValue function, these SQL statements will do it:

SQL code: TotalSales example.doc


SET @TerritoryList = ‘-1’ will return:

Note: The ‘(All)’-entity cannot be used in combination with other entities.
‘-1,2,4’ will not work, because TerritoryID ‘-1’ is not an existing ID.