Search for Value in Multiple Columns SQL Server T-SQL

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)

Advertisements
This entry was posted in Programming and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s