Resultset output for an SQL script becomes variable when control-of-flow keyword like IF and WHILE are used.


The resultsets returned by ADO.NET must be constant: The SQL script must always produce the same number of resultsets with exactly the same columns.


There can not be any difference in the resultsets (and columns) produced by the SQL script at different moments.

The resultset output at code generation time must exactly match the output at runtime.

Control-of-Flow keywords

The examples below are based on Transact-SQL.


The availability of keywords and their syntax can differ per ADO.NET Provider.


Transact-SQL control-of-flow language keywords are:

  • IF...ELSE
  • WHILE
  • GOTO label
  • RETURN
  • THROW
  • TRY...CATCH


When you put a SELECT statement inside an IF or WHILE then there might be problem.


Here are examples of control-of-flow to avoid:

IF keyword

Depending on the current year a different resultset is returned.


IF YEAR(GETDATE()) = 2018

 SELECT * FROM Customer

ELSE

 SELECT * FROM Employee

WHILE keyword

Here the number of resultsets returned is depending on the day of the week.


DECLARE @counter INT;

SET @counter = 0;


WHILE @counter <= DATEPART(weekday,GETDATE())

BEGIN

  SELECT * FROM INVOICE

  SET @counter = @counter + 1;

END;

CASE is fine

Using the CASE keyword in a SELECT statement is not a problem as SQL server will always return the same column definition. The same name, the same data type.


SELECT ProductNumber, Category =  

     CASE ProductLine  

        WHEN 'R' THEN 'Road'  

        WHEN 'M' THEN 'Mountain'  

        WHEN 'T' THEN 'Touring'  

        WHEN 'S' THEN 'Other sale items'  

        ELSE 'Not for sale'  

     END,  

  Name  

FROM Production.Product