regarding validations in stored procedure
-
Hi all,
1.I have input parameters like @age,@date, @time And @gender and output parameter was @paid amt.
2. I have a conditions like
a)between monday to friday(upto 9 pm) the doctor consultation fee was like
Above 10 years -- 200 rs
between 5-10 years -- 100 r.s
below 5 years-- 0 r.s
b) On saturdays the doctor consultation fee was like
between 0-10 years -- 300 r.s
above 10 years-- 500 r.s
c) On sundays the doctor consultation fee was like
between 0-10 years -- 300 r.s
above 10 years-- 700 r.s
d) on nights the consultation fee was like
between 0-10 years -- 200 r.s
above 10 years-- 500 r.s
so i want to write validation in a storedprocedure to met the above conditions.
please help me out from this task.
Thanks in Advance,
Avinash P
-
You've already got the pseudo code for these which is a good start.
My first question is, is the stored procedure the best place for doing this - would it be better placed in the application?
Otherwise it is a case of writing a few "if"s e.g.
IF @age > 9BEGIN
IF @paidamt = 200
BEGIN
SELECT
1
-- process for age at least 10 and 200 paid
END
ELSE
BEGIN
RAISERROR ('Incorrect parameters',10,1)
END
END
and so on.
Good luck
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
can u explain it briefly please i couldnt understand this.
-
DECLARE @age SMALLINT = 11,@date DATE = '2013-12-07',
@time TIME = '02:00',
@gender CHAR(1) = 'M',
@paid_amt SMALLINT;
SELECT
@paid_amt =
CASE
WHEN DATENAME(DW, @date) = 'Sunday' THEN
CASE
WHEN @Age <= 10 THEN 300
ELSE 700
END
WHEN DATENAME(DW, @date) = 'Saturday' THEN
CASE
WHEN @Age <= 10 THEN 300
ELSE 500
END
ELSE
CASE
WHEN @time < '06:00' OR @time > '21:00' THEN
CASE
WHEN @age <= 10 THEN 200
ELSE 500
END
ELSE
CASE
WHEN @age < 5 THEN 0
WHEN @age >= 5 AND @age <= 10 THEN 100
ELSE 200
END
END
END
-
SELECT
@paid =
CASE WHEN day_of_week = 5 /*Sat*/ THEN CASE WHEN @age <= 10 THEN 300 ELSE 500 END
WHEN day_of_week = 6 /*Sun*/ THEN CASE WHEN @age <= 10 THEN 300 ELSE 700 END
WHEN DATEPART(HOUR, @time) >= 21 /*night*/ THEN CASE WHEN @age <= 10 THEN 200 ELSE 500 END
ELSE /*Mon-Fri,day*/ CASE WHEN @age <= 5 THEN 0 WHEN @age <= 10 THEN 100 ELSE 200 END
END
FROM (
--determine day_of_week using method that *always* works, regardless of language or date settings
SELECT DATEDIFF(DAY, 0, @date) % 7 AS day_of_week --0=Mon;1=Tue;etc.
) AS generated_data
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply