SQL Server: use CASE with LIKE

sql sql-server stored-procedures case sql-like
Mike picture Mike · May 7, 2014 · Viewed 114.3k times · Source

I am pretty new to SQL and hope someone here can help me with this.

I have a stored procedure where I would like to pass a different value depending on whether a column contains a certain country or not.

So far I only used CASE when checking for the match with a specific number or value so I am not sure about this one. Can someone tell me if the following is valid and correct or let me know how to write this properly (just regarding the part in brackets) ?

(CASE countries
     WHEN LIKE '%'+@selCountry+'%' THEN 'national'
     ELSE 'regional') AS validity

Notes: @selCountry is the variable name of a country, countries can either be empty, one country or several countries separated with comma and space. Basically I just want to check if countries contains @selCountry and if yes, set validity to 'national'.

Answer

dean picture dean · May 7, 2014

This is the syntax you need:

CASE WHEN countries LIKE '%'+@selCountry+'%' THEN 'national' ELSE 'regional' END

Although, as per your original problem, I'd solve it differently, splitting the content of @selcountry int a table form and joining to it.

Related questions

Dynamic order direction

I writing a SP that accepts as parameters column to sort and direction. I don't want to use dynamic SQL. The problem is with setting the direction parameter. This is the partial code: SET @OrderByColumn = 'AddedDate' SET @OrderDirection = 1; … ORDER BY …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More