Can I define an in-cycle variable in T-SQL SELECT (like LET in LINQ)?

Question

I can write something like this with LINQ:

var selection = from person in personList
                let initials = person.FirstName[0] + person.LastName[0]
                select initials;

Can I do something similar with SQL, like maybe:

SELECT @Initials
FROM [Person]
SET @Initials = SUBSTRING (Person.FirstName, 1, 1) + SUBSTRING (Person.LastName, 1, 1)

Probably not but maybe there is a trick?

I need to have a precalculated variable for further use in a complex WHERE clause to avoid extreme complexity and code repetition.

Answer 1

I'm 11 years late, but incase anyone else comes across the thread, you can accomplish LET type functionality using CROSS APPLY.

SELECT Calculations.Initials
FROM [Person]
CROSS APPLY(SELECT SUBSTRING (Person.FirstName, 1, 1) + SUBSTRING (Person.LastName, 1, 1) as Initials) as Calculations

Answer 2

A clean way of doing this without adding a temporary table, looping, etc. would be with a Common Table Expression (CTE). Example:

;WITH PersonsWithInitials AS
(
    SELECT
        SUBSTRING (COALESCE(Person.FirstName,''), 1, 1)
        + SUBSTRING (COALESCE(Person.LastName,''), 1, 1) AS Initials,            
        FirstName,
        LastName,
        City
    FROM
        [Person]
)
SELECT
    FirstName,
    LastName,
    City,
    Initials
FROM
    PersonsWithInitials
WHERE
    /* Complex WHERE clause goes here and it can reference Initials as if it were a field */

In place of empty '' above, you could use a period or something else to stand in for null name fields.

This should all be executed in a single SQL call from .NET -- the CTE is not saved in the database like a view, stored procedure, temp table, etc.