Same fields from different tables using case statement in SQL Server 2005

Question

Hello friends,

I have a not so hard problem for SQL Server Expertise. Although I have another alternative but i want to know this can solved using CASE statement in SQL Server 2005.

I have various category tables with same columns. What i need is to call SELECT statement based on category. Below is the code what i am trying to do

ALTER PROCEDURE [dbo].[Sgi_DropDownListItemsLoadByTblName] 
    @Category int
AS
BEGIN
    SET NOCOUNT ON;


    CASE
        when @Category = 1 Then Select [ID],[OptionText],[Description] From dbo.Sgi_ListItems_Denomination
        when @Category = 2 Then Select [ID],[OptionText],[Description] From dbo.Sgi_ListItems_Drink
        when @Category = 3 Then Select [ID],[OptionText],[Description] From dbo.Sgi_ListItems_Education
        when @Category = 4 Then Select [ID],[OptionText],[Description] From dbo.Sgi_ListItems_Ethnicity
        when @Category = 5 Then Select [ID],[OptionText],[Description] From dbo.Sgi_ListItems_Kids
        when @Category = 6 Then Select [ID],[OptionText],[Description] From dbo.Sgi_ListItems_Religion
        when @Category = 7 Then Select [ID],[OptionText],[Description] From dbo.Sgi_ListItems_Smoking
    end
END

When I am trying to create this SP an error is generated on CASE statement

I know this can be done using simply IF statement, but i am eager to know how it can be done using CASE statement.

Can anybody help me.

Thanks for sharing your time.

Answer 1

Well, you could use dynamic sql, but I don't think what you're looking for is possible as written. However, someone could surprise me. See below for the quick and dirty version.

DECLARE @Statement varchar(max)
SET @Statement = 'Select [ID],[OptionText],[Description] From '

DECLARE @Category int
SET @Category = 5

SELECT @Statement = @Statement + 
    CASE
        when @Category = 1 Then 'dbo.Sgi_ListItems_Denomination'
        when @Category = 2 Then 'dbo.Sgi_ListItems_Drink'
        when @Category = 3 Then 'dbo.Sgi_ListItems_Education'
        when @Category = 4 Then 'dbo.Sgi_ListItems_Ethnicity'
        when @Category = 5 Then 'dbo.Sgi_ListItems_Kids'
        when @Category = 6 Then 'dbo.Sgi_ListItems_Religion'
        when @Category = 7 Then 'dbo.Sgi_ListItems_Smoking'
    end

EXEC(@Statement)