Dynamic WHERE Clause

SQL Apprentice Question
have a query which has a few different Time Period columns:
Half_Year (H1,H2)
Quarters (Q1,Q2,Q3,Q4)
Months (M1,M2,M3,.... M12)


These periods are held in three difference columns.


I need to run this query with 2 params. One will be the year and other will
be one of the above three:


i.e
sp_Rating 2005, 'H1'


This is all transactions in months 1=6 for the year 2005.


or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'


How can I dynamically interrogate the correct column, based on the param
supplied (H, Q, M) ?


Celko Answers
First of all, don't call your procedures sp_something. That prefix is
reserved for system procedures, and SQL Server first looks for a
procedure with such a name in the master database.


>> I have a query which has a few different Time Period columns: <<


Really? Mind showing us? Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.


>> These periods are held in three difference columns. <<


Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
time in the universe. Try a proper design:

CREATE TABLE PeriodCalendar
(period_name CHAR(15) NOT NULL PRIMARY KEY
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL);


INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
etc. for all the possible fiscal, marketing and calendaral periods you
use.



>> I need to run this query with 2 params. One will be the year and other will be one of the above three: sp_Rating 2005, 'H1' <<


Again, not a good design; you seem to think that a year should be an
integer, while the ANSI standard say it is a CHAR(4); get a copy of the
8601 standards, too. Now life a JOIN and a BETWEEN predicate.

And you have already been told about not using "sp_" prefixes because
they refer to where something is phycially located as well as having a
special meaning in SQL Server.