sql常用判断语句

来源：互联网发布：sql select 新表编辑：程序博客网时间：2024/06/09 06:09

1:case when then else end

示例一

SELECT 学号, 姓名,
等级=
CASE
WHEN 总学分 IS NULL THEN ‘尚未选课’
WHEN 总学分 < 50 THEN ‘不及格’
WHEN 总学分 >=50 and 总学分<=52 THEN ‘合格’
ELSE ‘优秀’
END FROM XS WHERE 专业名=’计算机’

示例二

update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end

示例三

select a, (case a when 1 then '中' else '国' end) AS B from table1

2:isnull()

isnull(sum(incount),0)

3:存储过程中执行存储过程

a:直接执行存储过程

Exec [sp_OrderGetSendFee] @Cart_ID, 0, @User_ID, @tTotalPrice, @tSendFee, @tSendFee OutPut

b:执行sql字符串

Set @Str = 'Select @All = Count(*) From ('+ @SQL + ') t'
Exec sp_ExecuteSQL @Str, N'@All Int OutPut', @TotalRecord OutPut

4：执行拼凑sql 语句

Exec sp_ExecuteSQL @Str

Original URL: http://www.cnblogs.com/zhangji/archive/2012/02/22/2363002.html