You are here:

MS SQL Server/IF/ELSE statement question in T-SQL

Advertisement


Question
Hello,

I was wondering how (if it is possible) to use an IF/ELSE statement from the results of a database query to perform other database queries in a Stored Procedure in SQL Server.
For example:

@start_date DATE

SELECT count(patientId) as patient_no
FROM patient
WHERE @start_date = start_date
AND end_date < '2014-01-01'

so my if/else statement would be using the result of the count as the boolean expression:

IF @patient_no is NULL

SELECT * FROM medicine

ELSE

SELECT * FROM doctor

Hope this makes sense!

Thanks

Tejus

Answer
It is easy to do what you want. You just have a couple of syntactical errors in your example.

First, in a SELECT clause when you use the "AS XXX" syntax, all that does is change or set the column name in the result set.  Quick example:
SELECT LastName FROM Customers...
would produce a result set of like this:
Customers
---------
Jones
Adams
Smith

Whereas SELECT LastName AS LName FROM Customers...
would produce this:
LName
---------
Jones
Adams
Smith

As a quick aside, this will produce the same thing:
SELECT LName = LastName FROM Customers



So, to directly answer your question, to get the value from a SELECT statement into a @Variable, you would use this:

================================================
DECLARE @start_date AS DATETIME --Note the missing "DECLARE" keyword in your example
DECLARE @patient_no AS INTEGER

SELECT @patient_no = count(patientId)
FROM patient
WHERE start_date = @start_date --I reversed these. It doesn't matter, but I find this more syntactically logical
AND end_date < '2014-01-01'

IF @patient_no = 0 -- a COUNT will return some number... "0" if no rows match the criteria
SELECT * FROM medicine
ELSE
SELECT * FROM doctor

================================================

One thing to note: When assigning SELECT values to variables, your SELECT clause should be constructed to always return 1 or 0 rows (Which a COUNT function will do in the absence of a GROUP BY, so your example is fine). If you did this:
SELECT @PatientID = PatientID
FROM patient
WHERE start_date = @start_date
it might return more than 1 row. I believe most or all versions of SQL Server will assign a value to @PatientID, but I'm not sure if it's the 1st value SQL encounters, or the last, or if it's unpredictable. In any event, you should avoid this.  


Finally, you can assign values to multiple @Variables with a single SELECT:
SELECT @PatID = PatientID, @StartDT = start_date, @EndDT = end_date....
FROM patient


Please let me know if you have followup questions on this issue.

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.