Careful with Control-of-Flow keywords
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.
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:
- GOTO label
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:
Depending on the current year a different resultset is returned.
IF YEAR(GETDATE()) = 2018
SELECT * FROM Customer
SELECT * FROM Employee
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())
SELECT * FROM INVOICE
SET @counter = @counter + 1;
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 =
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'