Switch case in a stored procedure

Question

0.00/5 (No votes)

See more:

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

Posted 26-Oct-12 1:44am

Member 9497070

Add a Solution

2 solutions

Solution 1

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

Posted 26-Oct-12 1:47am

Ambesha

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

damodara naidu betha · Accepted Answer · 2012-10-26T02:22:00

Replace

SQL

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

SQL

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