0

I'm trying to create a query that depending on an external variable length will run a query with a different WHERE clause. 

psuedo code:

bnum is a value entered by a user

IF bnum is 4 characters long 
SELECT Col1 from table Where Col2 = bnum
ELSE bnum is 5 characters long
SELECT Col1 from table where Col3 = bnum

At this point it would be ideal to be able to judge the length and run the appropriate query but I'm open to other options as my approach may be totally off.

I've tried a basic select statement with two where clauses but get invalid column. Tried using EXISTS but it returns all records. It seems that the LEN() function has to be a result of a query, so that doesn't seem to be an option.

forpas
  • 160,666
  • 10
  • 38
  • 76
Neil B.
  • 63
  • 7

2 Answers2

1

You need a CASE statement in the WHERE clause:

SELECT col1 from table
WHERE bnum = case len(bnum) 
    when 4 then col2
    when 3 then col3
end
forpas
  • 160,666
  • 10
  • 38
  • 76
0
SELECT Col1 from table Where Col2 = bnum and LEN(bnum)=4
UNION
SELECT Col1 from table where Col3 = bnum and LEN(bnum)=5

This is really a design issue though. You should not be building a query to pull a different column based on length of a value.

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51