SQL CASE and local variables
Solution 1
Two ways to use CASE in this scenario with MSSQL
DECLARE
@test int,
@result char(10)
SET @test = 10
SET @result = CASE @test
WHEN 10 THEN
'OK test'
ELSE
'Test is not OK'
END
PRINT @result;
SET @result = CASE
WHEN @test = 10 THEN
'OK test'
ELSE
'Test is not OK'
END
PRINT @result
Solution 2
try this:
DECLARE @Test int;
DECLARE @Result char(10);
SET @Test = 10;
select @Result=
CASE @Test
WHEN 10 THEN 'OK test'
END
Print @Result;
Solution 3
In SQL Server I would write it like this:
DECLARE @Test int;
DECLARE @Result char(10);
SET @Test = 10;
SET @Result = CASE @Test
WHEN 10
THEN 'OK test'
END
Print @Result;
The WHEN
clause does not have @Test = 10
, as the @Test
variable is stated in the CASE
clause.
See the CASE
documentation for SQL Server.
![GibboK](https://i.stack.imgur.com/Labo5.png?s=256&g=1)
GibboK
A professional and enthusiastic Senior Front End Developer. Listed as top 2 users by reputation in Czech Republic on Stack Overflow. Latest open source projects Animatelo - Porting to JavaScript Web Animations API of Animate.css (430+ stars on GitHub) Industrial UI - Simple, modular UI Components for Shop Floor Applications Frontend Boilerplate - An opinionated boilerplate which helps you build fast, robust, and adaptable single-page application in React Keyframes Tool - Command line tool which convert CSS Animations to JavaScript objects gibbok.coding📧gmail.com
Updated on July 05, 2022Comments
-
GibboK about 2 years
I would like to know how I can use local variables in
CASE
statements in SQL?This script gives me an error:
DECLARE @Test int; DECLARE @Result char(10); SET @Test = 10; CASE @Test WHEN @Test = 10 THEN SET @Result='OK test' END Print @Result;
I use MS SQL 2008.
-
Tobiasopdenbrouw almost 14 yearsThis does not work in my SQL 2005 db. (incorrect syntax near the keyword 'Case'). (I had the same solution, but it didn't work).
-
Evil Pigeon almost 14 yearsThe syntax here is wrong. You can't use a case statement like an if statement.
-
Tobiasopdenbrouw almost 14 yearsanishmarokey's answer is a bit cleaner as it retains your @result variable.