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

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

WITH PersonsWithInitials AS (SELECT
     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.

Answer 2

What's wrong with:

SELECT SUBSTRING (Person.FirstName, 1, 1) + SUBSTRING (Person.LastName, 1, 1) AS Initials
FROM [Person]

Answer 3

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

or

SELECT SUBSTRING (Person.FirstName, 1, 1) 
       + SUBSTRING (Person.LastName, 1, 1)
            AS Initials
FROM [Person]

If you need to use it later, a clearly readable way is to use a common table exprssion (which can be stacked, instead of nested):

WITH Person2 AS (
    SELECT SUBSTRING (Person.FirstName, 1, 1) 
           + SUBSTRING (Person.LastName, 1, 1)
                AS Initials
    FROM [Person]
)
SELECT Initials, COUNT(*) AS Record_Count
FROM Person2
GROUP BY Initials

Answer 4

Looks like you're just trying to get the initials into a variable for later. Try it this way...

DECLARE @Initials varchar(2)

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

If you're trying to use that as a set and not a single value, you should look at doing a subquery or CTE with your other complex query.

Answer 5

if you want to keep a set of initials to use multiple times, or as part of a larger query, do the following:

declare @TableVariable  table (Initials varchar(200)

INSERT INTO @TableVariable  
        (Initials)
    SELECT
        ISNULL(SUBSTRING(Person.FirstName,1,1),'')+ISNULL(SUBSTRING(Person.LastName,1,1),'')
    FROM Person

SELECT * FROM @TableVariable

SELECT * FROM @TableVariable

if you are doing many rows #temp tables are faster. Also, it would probably be faster to just make this a derived table in a bigger query than using a temp table.