问题

我正在使用存储过程（SQL Server），其中@classtype是一个参数，我想要当classtype = p时，classtype的值来自表格，但列名为English或Other，根据classtype不同而定。我尝试以下代码来获取数据，但出现错误：

> AS不受支持。
```SQL
select
  col
  , col1
  , CASE WHEN @classtype = 'P' THEN classType ELSE 'English' END AS 'English'
    , CASE WHEN @classtype = 'P' THEN col4 ELSE 'Other' END AS 'Other'
from my_table

谢谢。

<details>
<summary>英文:</summary>

I am using a stored procedure (SQL Server) from get value where @classtype is a parameter, I want to when classtype = p then classType value come from table but column name English or Other base on classtype, I am trying below code for get data but get error for:

&gt; AS not supported.   

select
col
, col1
, CASE WHEN @classtype ='P' THEN classType AS 'English' ELSE classType as 'Other' END, col4
from my_table

Thanks.

</details>


# 答案1
**得分**: 1

你可以使用`IF`语句来返回不同的结果集。
```sql
IF @ClassType = 'P' BEGIN
    SELECT
        col
        , col1
        , classType AS English
        , col4
    FROM my_table;
END; ELSE BEGIN
    SELECT
        col
        , col1
        , classType AS Other
        , col4
    FROM my_table;
END;

或者使用动态SQL

DECLARE @Sql nvarchar(max) = 
'SELECT
    col
    , col1
    , classType AS ' + CASE WHEN @ClassType = 'P' THEN 'English' ELSE 'Other' END + '
    , col4
FROM my_table;';

EXEC sp_execute @Sql;

IF @ClassType = 'P' BEGIN
    SELECT
        col
        , col1
        , classType AS English
        , col4
    FROM my_table;
END; ELSE BEGIN
    SELECT
        col
        , col1
        , classType AS Other
        , col4
    FROM my_table;
END;

DECLARE @Sql nvarchar(max) = 
'SELECT
    col
    , col1
    , classType AS ' + CASE WHEN @ClassType = 'P' THEN 'English' ELSE 'Other' END + '
    , col4
FROM my_table;';

EXEC sp_execute @Sql;

答案2

得分: 0

你可以使用动态SQL

DECLARE @classtype CHAR(1);
DECLARE @alias CHAR(10);
DECLARE @sql CHAR(1000);
SET @classtype = 'P';
IF (@classtype = 'P')
BEGIN
	SET @alias = 'English';
END
ELSE
BEGIN
	SET @alias = 'Other';
END
SET @sql = 'select
    col
    , col1
    , classType as ' + @alias +
    ', col4
from my_table';

EXEC (@sql);

DECLARE @classtype CHAR(1);
DECLARE @alias CHAR(10);
DECLARE @sql CHAR(1000);
SET @classtype = 'P';
IF (@classtype = 'P')
BEGIN
	SET @alias = 'English';
END
ELSE
BEGIN
	SET @alias = 'Other';
END
SET @sql = 'select
    col
    , col1
    , classType as ' + @alias +
    ', col4
from my_table';

EXEC (@sql);