rivcomp (5/17/2016)
Veteran,this is probably not the best way to use a CTE, a CTE is means to be re-usable across the T-SQL query and it is not meant to switch tables and columns on the fly, for this last it is better to use other techniques, like conditions with an IF statement or just a dynamic query:
-- Sub Query Technique:
CREATE PROCEDURE MYProcedure
@Type = 1
IF @Type = 1
BEGIN
SELECT * FROM TABLE 1
END
ELSE IF @Type = 2
BEGIN
SELECT * FROM TABLE 2
END
ELSE
BEGIN
SELECT * FROM TABLE 3
END
Regards,
First, you do realize that this thread is over six years old and the OP hasn't been active in over a year, so he is very unlikely to see this.
Second, the OP already stated that he had decided to use an approach that was very similar to the approach that you outlined here.
Finally, I would disgree that CTEs are meant to be reusable. CTEs are meant to be a better version of derived tables, and being reusable is only one minor way in which they are better. Being reusable is not a defining characteristic of a CTE.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA