WHERE Clause Using Different Column Passed By Parameter

Hi all,

I'd think this is possible but I'm having trouble getting data returned from the query. Fields PART_NUMBER and INTERNAL_SKU exist in the SKU table. This will be inside a SP. If user passes 'PN' as first parameter then I'd need to have the WHERE clause by PART_NUMBER, if he passes 'SK' (or anything else for now) then the WHERE clause shold be by SKU.

Can't I just build the WHERE by replacing @SearchField with its value ? I've looked up the CASE statement but I don't think it does what I need.


DECLARE @strSearchType varchar(2)

DECLARE @strSearchValue varchar(15)

DECLARE @SearchField varchar(15)

set @strSearchType = 'PN'

set @strSearchValue = '1234567'


IF @strSearchType = 'PN'

begin

set @SearchField = 'PART_NUMBER'

end

ELSE

begin

set @SearchField = 'INTERNAL_SKU'

end

SELECT

SKU as 'SKU',

PART_NUMBER as 'PartNumber',

DESCRIPTION as 'Description'

FROM SKUs

WHERE @SearchField = @strSearchValue

FOR XML PATH('SKU'), ROOT('Response')


Thanks,

ST