question

nachoshaw-9496 avatar image
0 Votes"
nachoshaw-9496 asked nachoshaw-9496 commented

SQL WHERE Clause with SELECT CASE

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




sql-server-generalsql-server-transact-sqlazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered nachoshaw-9496 commented

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)
 )
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi

Thanks for that nice and simple solution. It works the same without the 1=1 AND. I updated it slightly to this so that i could return all rows if a null value was passed

 SELECT *
  FROM dbo].[tblMyFastenerTable]
  WHERE (
      (@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) OR
      (@curb_material IS NULL))

Then call the parameter like this for a material filter

 [tblMyFastenerTable] 'STEEL'

or like this for all results

 [tblMyFastenerTable]


Thanks so much :)


0 Votes 0 ·