DELIMITER $$ CREATE PROCEDURE proc_reserve_nanhang() BEGIN select (select count(table_ticket.fight_no ) from table_ticket WHERE fight_no LIKE "南航CZ3172")as num1, (select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "南航CZ3172" )as num2, case when (select count(table_ticket.fight_no) from table_ticket WHERE fight_no LIKE "南航CZ3172") =(select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "南航CZ3172" ) then '未售空' when (select count(table_ticket.fight_no ) from table_ticket WHERE fight_no LIKE "南航CZ3172") >(select table_air.air_setting from table_air INNER JOIN table_fight ON table_fight.air_model=table_air.air_model WHERE fight_no LIKE "南航CZ3172" ) then '已售空' else '未售空' end as '南航CZ3172机票状态'; END $$ DELIMITER ; CALL proc_reserve_nanhang(); 改写为sql server语言
时间: 2023-11-12 15:05:33 浏览: 95
pandas.read_csv参数详解(小结)
以下是将MySQL存储过程改写为SQL Server存储过程的代码:
```
CREATE PROCEDURE proc_reserve_nanhang
AS
BEGIN
SELECT
(SELECT COUNT(fight_no) FROM table_ticket WHERE fight_no LIKE '南航CZ3172') AS num1,
(SELECT air_setting FROM table_air INNER JOIN table_fight ON table_fight.air_model = table_air.air_model WHERE fight_no LIKE '南航CZ3172') AS num2,
CASE
WHEN (SELECT COUNT(fight_no) FROM table_ticket WHERE fight_no LIKE '南航CZ3172') = (SELECT air_setting FROM table_air INNER JOIN table_fight ON table_fight.air_model = table_air.air_model WHERE fight_no LIKE '南航CZ3172')
THEN '未售空'
WHEN (SELECT COUNT(fight_no) FROM table_ticket WHERE fight_no LIKE '南航CZ3172') > (SELECT air_setting FROM table_air INNER JOIN table_fight ON table_fight.air_model = table_air.air_model WHERE fight_no LIKE '南航CZ3172')
THEN '已售空'
ELSE '未售空'
END AS '南航CZ3172机票状态';
END
```
需要注意的是,在 SQL Server 中不需要使用 `DELIMITER` 命令来指定语句分隔符,因此在这个存储过程中将其删除了。此外,SQL Server 中不支持在 `SELECT` 语句中使用 `AS` 关键字来为列设置别名,因此需要将别名移动到 `END AS` 子句中。
阅读全文