Database programming
Functions, IF and WHILE
Think in sets before loops.
Understand the concept
Scalar functions return a value; inline table-valued functions return a parameterised table expression. Inline TVFs can participate in relational optimisation. Scalar UDF costs depend on version and inlining eligibility; measure expensive per-row calls. Functions and procedures have different side-effect restrictions.
IF/ELSE chooses control flow, whereas CASE chooses an expression value. WHILE and cursors have valid operational uses, but set-based UPDATE or INSERT is often clearer for many rows. A loop needs explicit progress and termination. The small loop here demonstrates control flow, not a recommended data-processing architecture.
Use parameterised reading logic and controlled flow.
Using a loop for every row as if SQL were an application language.
One topic. Three levels of depth.
IF EXISTS (SELECT 1 FROM lab.Product WHERE Stock = 0) SELECT N'Replenishment needed' AS Message;Returns a result when the condition holds.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Find zero-stock products without a loop.
Show the solution and explanation
SELECT ProductId, ProductName FROM lab.Product WHERE Stock = 0;Returns Cable; the set-based query expresses the rule directly.