Incorrect syntax CASE TSQL in SQL server 2014

Question

Incorrect syntax CASE TSQL in SQL server 2014

Navigation

#1 by (4 votes)

1

My problem is this: I have to make a script in TSQL that if the numbers are less than 50, you must make the root of the sum of its squares, if it is equal to 50, add its squares, and if it is greater than 50, divide the first one between the product of the first two.

I've made this script:

/*Condición si la suma es menor de 50 hacer raíz cuadrada de los cuadrados, si */
DECLARE
    @num smallint;
    SET @num = 30;
DECLARE 
    @num2 smallint;
    SET @num2 = 10;
DECLARE
    @num3 smallint;
    SET @num3 = 10;
DECLARE 
    @raiz decimal (4,3);
DECLARE
    @suma int;
DECLARE
    @cuadrado int;
DECLARE
    @div float;
BEGIN
    SET @suma = @num+@num2+@num3;
    IF (@suma < 50)
        BEGIN
            SET @raiz = sqrt(@num^2+@num2^2+@num3^2);
            print @raiz;
        END
    IF (@suma = 50)
        BEGIN
            SET @cuadrado = @num^2+@num2^2+@num3^2;
            print @cuadrado;
        END
    IF (@suma > 50)
        BEGIN
            SET @div = @num/(@num2*@num3);
            print @div;
        END
END

It does not give an error, but it does not work well when the number is equal to 50, nor when it is greater, so I have considered an expression case :

DECLARE
    @num smallint;
    SET @num = 30;
DECLARE 
    @num2 smallint;
    SET @num2 = 10;
DECLARE
    @num3 smallint;
    SET @num3 = 10;
DECLARE 
    @raiz decimal (4,3);
DECLARE
    @suma int;
DECLARE
    @cuadrado int;
DECLARE
    @div float;
SET @suma = @num+@num2+@num3;

CASE
    WHEN(@suma < 50)
        THEN
            SET @raiz = sqrt(@num^2+@num2^2+@num3^2);
            print @raiz;

    WHEN (@suma = 50)
        THEN
            SET @cuadrado = @num^2+@num2^2+@num3^2;
            print @cuadrado;

    ELSE (@suma > 50)
        THEN
            SET @div = @num/(@num2*@num3);
            print @div;
END

sql-server tsql

asked by ras212 11.01.2017 в 22:30

source

1 answer

Problem counting correct answers Insert data into an array inside a json

score 4 · Accepted Answer

First of all, an expression CASE is not a loop, it is simply an expression that delivers a result.

What you need is quite simple:

DECLARE @num smallint, @num2 smallint, @num3 smallint;

SET @num = 30;
SET @num2 = 10;
SET @num3 = 10;

DECLARE @suma int;
SET @suma = @num+@num2+@num3;

SELECT  CASE 
            WHEN @suma < 50 THEN sqrt(@num^2+@num2^2+@num3^2)
            WHEN @suma = 50 THEN @num^2+@num2^2+@num3^2
            WHEN @suma > 50 THEN CAST(@num as float)/(@num2*@num3)
        END