在存储过程中切换大小写 [英] Switch case in a stored procedure

问题描述

你好，

我有一个具有以下结构的表Policy_details-

policy_no int主键，
tableno varchar(10)，
术语int，
sum_proposed int，
分期付款int，
p_dateback varchar(15)，
issue_date varchar(15)，
模式varchar(7)

我尝试根据用户选择的模式来获取分期付款的日期.每季度，半年和每年都有三种模式.

这是我的代码-

创建proc due_date
@pn int
如
开始
声明@tempdate datetime
声明@temp varchar(15)
声明@ temp1 varchar(15)
声明@duedate datetime
声明@md varchar(7)
从Policy_details中选择@ temp = issue_date，@ temp1 = p_dateback，@ md = mode，其中policy_no = @ pn
如果@temp为空
设置@ tempdate = @ temp1
其他
设置@ tempdate = @ temp
选择案例@md
当每季度然后选择@ duedate = DATEADD(month，3，@ tempdate)
当半年一次，然后选择@ duedate = DATEADD(month，6，@ tempdate)
当每年，然后选择@ duedate = DATEADD(month，12，@ tempdate)
以Due_Date结尾
结束

但是这段代码给了错误.请帮助我解决这些问题.谢谢您

Hello,

I have a table Policy_details that has following structure -

policy_no int primary key,
tableno varchar(10),
term int,
sum_proposed int,
installment int,
p_dateback varchar(15),
issue_date varchar(15),
mode varchar(7)

I tried to get the date on which the installment will be due on the basis of the mode selected by user.Three types of modes are there Quarterly,Half Yearly and Yearly.

Here is my code -

create proc due_date
@pn int
as
begin
declare @tempdate datetime
declare @temp varchar(15)
declare @temp1 varchar(15)
declare @duedate datetime
declare @md varchar(7)
select @temp=issue_date,@temp1=p_dateback,@md=mode from Policy_details where policy_no=@pn
if @temp is null
set @tempdate=@temp1
else
set @tempdate=@temp
select Case @md
when Quarterly then select @duedate=DATEADD(month,3,@tempdate)
when Half Yearly then select @duedate=DATEADD(month,6,@tempdate)
when Yearly then select @duedate=DATEADD(month,12,@tempdate)
end as Due_Date
end

But this code is giving errors. Kindly help me solving those.Thank you

推荐答案

不知道您究竟遇到了什么错误，但是请尝试以下操作:

SQL CASE表达式的简单使用 [ ^ ]
http://social.msdn.microsoft.com/Forums/zh/transactsql/thread/0b79fa4d-172e-4c31-a7d1-0c841445a633 [

Don''t know what error exactly you are getting, but try this:

A Simple Use of SQL CASE Expression[^]
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/0b79fa4d-172e-4c31-a7d1-0c841445a633[^]

Thanks,
Ambesha

替换

select Case @md
when Quarterly then select @duedate=DATEADD(month,3,@tempdate)
when Half Yearly then select @duedate=DATEADD(month,6,@tempdate) 
when Yearly then select @duedate=DATEADD(month,12,@tempdate)
end as Due_Date

与

with

SELECT @duedate = Case WHEN @md = 'Quarterly' then DATEADD(month,3,@tempdate)
WHEN @md= 'Half Yearly' then DATEADD(month,6,@tempdate)
WHEN @md= 'Yearly' then DATEADD(month,12,@tempdate)
END

NOTE:- Change @md varchar(7) to varchar(12)

谢谢

Thank you

这篇关于在存储过程中切换大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！