SQL WHERE Clause with SELECT CASE

Question

Hi

I have to alter an existing table with a new type of incoming value. Basically, the return rows are conditional on a material type but the existing table holds a bit value for the material instead of material type as a string. The reason is because the table returns a row of fasteners and the same fastener can be used on multiple materials. There is a column for each material and boolean yes/no if its used with the material.

There are 5 materials (Wood, Steel, Aluminum, Masonry, ALL)

My current procedure i set up has a range of If conditions with the full SELECT FROM WHERE for each if but id like to know if i can reduce the amount of code to something like this-

ALTER PROC [dbo].[tblMyFastenerTable]

@curb_material varchar(MAX) = NULL

BEGIN TRAN

SELECT *
FROM dbo].[tblMyFastenerTable]

WHERE CASE
WHEN @curb_material = 'Steel' THEN [dbo].[tblMyFastenerTable].Steel = 1
WHEN @curb_material = 'Wood' THEN [dbo].[tblMyFastenerTable].Wood = 1
WHEN @curb_material = 'Aluminum' THEN [dbo].[tblMyFastenerTable].Aluminum = 1
WHEN @curb_material = 'Masonry' THEN [dbo].[tblMyFastenerTable].Masonry = 1
ELSE
END

COMMIT

I dont have the syntax right but the statement or something similar is what im trying achieve. Unless of course its not achievable and the best way is to use the mutliple SLECT statements with IF.

Any help is greatly appreciated :)

Thanks

Answer 1

You do not need to use BEGIN TRAN ... COMMIT if you just want to SELECT. Try this:

SELECT *
FROM dbo].[tblMyFastenerTable]
WHERE 1 = 1 
AND (
    (@curb_material = 'Steel' AND Steel = 1) OR 
    (@curb_material = 'Wood' AND Wood = 1) OR
    (@curb_material = 'Aluminum' AND Aluminum = 1) OR
    (@curb_material = 'Masonry' AND Masonry = 1)
)