mysql - T-SQL 存储过程 CASE 问题

Question

你能告诉我我做错了什么吗？

CREATE PROC [dbo].[GetUserEULASelection] (@username VARCHAR(150))
AS
BEGIN
    DECLARE @result VARCHAR(250);

    SELECT @result = a.NAME
    FROM licmn_license_users_accounts_c ua
    JOIN accounts a ON a.id = ua.licmn_license_users_accountsaccounts_ida
    JOIN licmn_license_users lu ON lu.id = licmn_license_users_accountslicmn_license_users_idb
    WHERE lu.username = @username;

    CASE 
        WHEN @result IS NOT NULL
            THEN SELECT @result AS 'name'
        END
    CASE 
        WHEN @result IS NULL
            THEN SELECT '0' AS 'name'
    END
END

我是 T-SQL 的新手(我正在将 MySQL 过程移植到 MSSQL)

问题是case语句导致编译错误。

Answer 1

问题是您错误地使用了 CASE。在这种情况下，您将它与 IF 混为一谈

所以你应该正确地做:

IF @result IS NOT NULL
   SELECT @result AS name
ELSE
    SELECT '0' AS name

如果你想使用 CASE，你应该在查询中使用它:

 SELECT CASE 
       WHEN a.NAME IS NULL THEN '0'
       ELSE a.NAME 
     END as Name
    FROM licmn_license_users_accounts_c ua
    JOIN accounts a ON a.id = ua.licmn_license_users_accountsaccounts_ida
    JOIN licmn_license_users lu ON lu.id = licmn_license_users_accountslicmn_license_users_idb
    WHERE lu.username = @username;