SyntaxFix

[sql] Multiple conditions with CASE statements

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?

Examples related to sql

• Passing multiple values for same variable in stored procedure • SQL permissions for roles • Generic XSLT Search and Replace template • Access And/Or exclusions • Pyspark: Filter dataframe based on multiple conditions • Subtracting 1 day from a timestamp date • PYODBC--Data source name not found and no default driver specified • select rows in sql with latest date for each ID repeated multiple times • ALTER TABLE DROP COLUMN failed because one or more objects access this column • Create Local SQL Server database

Examples related to sql-server

• Passing multiple values for same variable in stored procedure • SQL permissions for roles • Count the Number of Tables in a SQL Server Database • Visual Studio 2017 does not have Business Intelligence Integration Services/Projects • ALTER TABLE DROP COLUMN failed because one or more objects access this column • Create Local SQL Server database • How to create temp table using Create statement in SQL Server? • SQL Query Where Date = Today Minus 7 Days • How do I pass a list as a parameter in a stored procedure? • SQL Server date format yyyymmdd

Examples related to case

• PostgreSQL CASE ... END with multiple conditions • SQL Server: use CASE with LIKE • SQL Server IIF vs CASE • SELECT using 'CASE' in SQL • SELECT query with CASE condition and SUM() • GROUP BY + CASE statement • SQL use CASE statement in WHERE IN clause • SQL Server - Case Statement • where to place CASE WHEN column IS NULL in this query • Regarding Java switch statements - using return and omitting breaks in each case

Examples related to where-clause

• Laravel where on relationship object • MySQL Select last 7 days • SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel • Conditional WHERE clause in SQL Server • Conditional WHERE clause with CASE statement in Oracle • How to write a SQL DELETE statement with a SELECT statement in the WHERE clause? • MySQL Multiple Where Clause • How to use If Statement in Where Clause in SQL? • MySQL direct INSERT INTO with WHERE clause • MySql Inner Join with WHERE clause