CODEWITHSUNDEEP
c#linqt-sql
Gene
Gene

Reputation: 4242

Equivalent of LINQ's deferred execution in T-SQL

In LINQ one can make use of deferred execution to seperate different concerns from each other at design time. My provider will generate an optimal SQL statement to fetch the data. Consider the following example:

// some complex query to fetch matches goes here
IEnumerable<Match> data = null;

if (/* some condition */)
{
  data = from m in Context.Matches
         where /* ... */
         select m;
}
else 
{
  data = from m in Context.Matches
         join /* .. */
         join /* .. */
         where /* ... */
         select m;
}

// later on, order the data depending on some user selection
IOrderedEnumerable<Match> orderedData = null;

switch (someOption) 
{
  case /*...*/:
      orderedData = from m in data orderby by m.Start select m;
      break;
  case /*...*/
      orderedData = from m in data orderby by m.ID select m;
      break;
}

// do something with the computed data

Is there an efficient way to achieve the same functionality in a stored procedure without the need of building queries as a string? All I can think of are table variables, but I don't know about their performance implications:

CREATE @matches TABLE 
(
  ID int,
  -- ...
  Start datetime
)


-- some complex query to fetch matches goes here
IF -- some condition
  INSERT INTO @matches(ID, Start) 
       SELECT ID, ..., Start FROM Matches WHERE ...
ELSE
  INSERT INTO @matches(ID, Start) 
       SELECT ID, ..., Start FROM Matches JOIN ... JOIN ... WHERE ...

-- later on, order the data depending on some user selection
CREATE @orderedMatches TABLE 
(
  ID int,
  -- ...
  Start datetime
)

IF -- some option
  INSERT INTO @orderedMatches (ID, Start) 
       SELECT ID, ..., Start FROM @matches ORDER BY Start ASC
ELSE IF -- some option
  INSERT INTO @orderedMatches (ID, Start) 
       SELECT ID, ..., Start FROM @matches ORDER BY ID ASC

-- do something with the computed data

Upvotes: 0

Views: 135

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294407

Deferred execution does not exist in T-SQL. Any statement you use will be executed right then and there. The closest thing you can get is Dynamic-SQL: build a SQL text containing your logic, from pieces, then finally execute the SQL:

declare @sql nvarchar(max) = N'';

if <some condition>
 @sql += N'SELECT ...'

if <some other condition>
 @sql += N'INSERT ...'

-- finally, execute the SQL:
exec sp_executesql @sql;

Needless to say, this is nowhere near as powerfull as LINQ. Doing anything fancy like adding optional WHERE clauses will require complex logic to build the @sql script. It also results in code that is error prone and extremely difficult to debug. Read The Curse and Blessings of Dynamic SQL for a more thorough discussion.

SQLCLR is an alternative, if you're willing to go that path.

Upvotes: 2

Related Questions