Converting MySQL to TSQL using user defined variables

Question

I am new to using SQL Server and I am trying to convert this query to something I can use in SQL Server. I've been googling to find the right structure but I can't seem to see anything referring to 'user-defined-variables' at all.

Any help would be appreciated.

Overview of what I am trying to achieve...

I have 6 columns and I need to get the median of the 6 columns but only where there is at least 4 responses. Possible values in the field are 1-5 with 0 being a non-response.

SELECT
    GCAID,
    @countOfValuesGSS1 := (
        CASE 
           WHEN CEQ106 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
        CASE 
           WHEN CEQ114 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
        CASE 
           WHEN CEQ123 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
        CASE 
           WHEN CEQ132 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
        CASE 
           WHEN CEQ142 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
        CASE WHEN CEQ143 BETWEEN 1 AND 5 THEN 1 ELSE 0 END),
     @sumOfValuesGSS1 := (
     CASE 
        WHEN CEQ106 BETWEEN 1 AND 5 THEN CEQ106 ELSE 0 END + 
     CASE 
        WHEN CEQ114 BETWEEN 1 AND 5 THEN CEQ114 ELSE 0 END + 
     CASE 
        WHEN CEQ123 BETWEEN 1 AND 5 THEN CEQ123 ELSE 0 END + 
     CASE 
        WHEN CEQ132 BETWEEN 1 AND 5 THEN CEQ132 ELSE 0 END + 
     CASE  
        WHEN CEQ142 BETWEEN 1 AND 5 THEN CEQ142 ELSE 0 END + 
     CASE 
        WHEN CEQ143 BETWEEN 1 AND 5 THEN CEQ143 ELSE 0 END),
     if(@countOfValuesGSS1 >= 4, ROUND((@sumOfValuesGSS1/@countOfValuesGSS1),2), NULL) AS b_GSSMAJ1,
     MAJ1,
FROM
   v_ags_all

instead of "@countOfValuesGSS1 :=" use "countOfValuesGSS1" and replace IF with case statementp — BI Dude, Sep 25 '14 at 07:33

score 0 · Answer 1 · answered Sep 29 '14 at 22:51

SQL Server doesn't support the same type of inline variable concept, but it does support another construct called Common Table Expressions (CTE) that would require little change to the original query:

;WITH cte AS
(
    SELECT  GCAID,
            (
                CASE WHEN CEQ106 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
                CASE WHEN CEQ114 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
                CASE WHEN CEQ123 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
                CASE WHEN CEQ132 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
                CASE WHEN CEQ142 BETWEEN 1 AND 5 THEN 1 ELSE 0 END + 
                CASE WHEN CEQ143 BETWEEN 1 AND 5 THEN 1 ELSE 0 END
            ) AS countOfValuesGSS1,
            (
                CASE WHEN CEQ106 BETWEEN 1 AND 5 THEN CEQ106 ELSE 0 END + 
                CASE WHEN CEQ114 BETWEEN 1 AND 5 THEN CEQ114 ELSE 0 END + 
                CASE WHEN CEQ123 BETWEEN 1 AND 5 THEN CEQ123 ELSE 0 END + 
                CASE WHEN CEQ132 BETWEEN 1 AND 5 THEN CEQ132 ELSE 0 END + 
                CASE WHEN CEQ142 BETWEEN 1 AND 5 THEN CEQ142 ELSE 0 END + 
                CASE WHEN CEQ143 BETWEEN 1 AND 5 THEN CEQ143 ELSE 0 END
            ) AS sumOfValuesGSS1,
            MAJ1
    FROM v_ags_all
)

SELECT  GCAID,
        CASE WHEN countOfValuesGSS1 >= 4 THEN
            ROUND(sumOfValuesGSS1 / countOfValuesGSS1, 2)
        END AS b_GSSMAJ1,
        MAJ1
FROM cte;

CTEs basically act as on-the-fly views. They allow you to do sort of the same thing as you provided, albeit with a little more verbosity.

The other minor change was converting the inline IF statement to a CASE statement instead.

Converting MySQL to TSQL using user defined variables

1 Answers1