Jump to content


Check out our Community Blogs





- - - - -

Search for Value in Multiple Columns SQL Server T-SQL

  Posted by Barnsite, 04 December 2012 · 776 views

programming
Having to write some SQL SELECT’s on some un-normalized tables left me thinking there has to be a tidier way. I started with:

SELECT
*
FROM bags.SAC_LOG
WHERE [SCANNERID_1] = @SCAN_ID
OR [SCANNERID_2] = @SCAN_ID
OR [SCANNERID_3] = @SCAN_ID
OR [SCANNERID_4] = @SCAN_ID
OR [SCANNERID_5] = @SCAN_ID
OR [SCANNERID_6] = @SCAN_ID
OR [SCANNERID_7] = @SCAN_ID
OR [SCANNERID_8] = @SCAN_ID
OR [SCANNERID_9] = @SCAN_ID

(@SCAN_ID is a variable declared earlier)
I ended up with:

SELECT
*
FROM bags.SAC_LOG
WHERE @SCAN_ID IN
(
[SCANNERID_1],
[SCANNERID_2],
[SCANNERID_3],
[SCANNERID_4],
[SCANNERID_5],
[SCANNERID_6],
[SCANNERID_7],
[SCANNERID_8],
[SCANNERID_9]
)

Maybe not a massive improvement, but personally I find this easier to read and debug (especially if there are other conditions in the WHERE)
http://feeds.wordpre...dpress.com/504/ Posted Image

Source

  • 0