Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive ctes
Summary

Instruction

Oops, our query failed. That was unexpected. What happened?

If you carefully read the error message, it will tell you that the types in the anchor and the recursive parts of our query do not match.

The data type for

SELECT
  N''

is the data type for column X, i.e. Unicode String Literal.

The data type for

SELECT
  X + W

is NVARCHAR of certain length. Unicode String Literal and NVARCHAR types do not match. The UNION ALL operator expects the types of corresponding columns to match; when they don't, the query fails.

What can we do about it? We have to explicitly cast the value N'' to match the type of the other expression, like this:

SELECT
  CAST(N'' AS NVARCHAR(MAX))

The MAX argument makes sure that the type will be correct, because it forces the concatenation operator to return an NVARCHAR(MAX) type.

Unfortunately, there are no simple rules explaining what data type you must cast a column to get your query right.

Exercise

Fix the query so that it runs correctly.

Cast the value N'' to type NVARCHAR(MAX).