SQL WHERE Clause with SELECT CASE

NachitoMax 411 Reputation points
2022-04-11T21:18:52.083+00:00

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
0 comments No comments
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2022-04-11T21:33:07.36+00:00

    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)
    )
    

0 additional answers

Sort by: Most helpful