I need to query some data. here is the query that i have constructed but which isn't workig fine for me. For this example I am using AdventureWorks database.
SELECT * FROM [Purchasing].[Vendor] WHERE PurchasingWebServiceURL LIKE
case
// In this case I need all rows to be returned if @url is '' or 'ALL' or NULL
when (@url IS null OR @url = '' OR @url = 'ALL') then ('''%'' AND PurchasingWebServiceURL IS NULL')
//I need all records which are blank here including nulls
when (@url = 'blank') then (''''' AND PurchasingWebServiceURL IS NULL' )
//n this condition I need all record which are not like a particular value
when (@url = 'fail') then ('''%'' AND PurchasingWebServiceURL NOT LIKE ''%treyresearch%''' )
//Else Match the records which are `LIKE` the input value
else '%' + @url + '%'
end
This is not working for me. How can I have multiple where condition clauses in the THEN
of the the same CASE
? How can I make this work?
It's not a cut and paste. The CASE
expression must return a value, and you are returning a string containing SQL (which is technically a value but of a wrong type). This is what you wanted to write, I think:
SELECT * FROM [Purchasing].[Vendor] WHERE
CASE
WHEN @url IS null OR @url = '' OR @url = 'ALL'
THEN PurchasingWebServiceURL LIKE '%'
WHEN @url = 'blank'
THEN PurchasingWebServiceURL = ''
WHEN @url = 'fail'
THEN PurchasingWebServiceURL NOT LIKE '%treyresearch%'
ELSE PurchasingWebServiceURL = '%' + @url + '%'
END
I also suspect that this might not work in some dialects, but can't test now (Oracle, I'm looking at you), due to not having booleans.
However, since @url
is not dependent on the table values, why not make three different queries, and choose which to evaluate based on your parameter?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With