Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

regarding validations in stored procedure Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 12:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 28, 2014 10:53 PM
Points: 30, Visits: 70
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
Post #1509192
Posted Tuesday, October 29, 2013 2:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 2,824, Visits: 2,834
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 > 9 
BEGIN
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
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
Post #1509201
Posted Tuesday, October 29, 2013 3:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 28, 2014 10:53 PM
Points: 30, Visits: 70
can u explain it briefly please i couldnt understand this.
Post #1509227
Posted Tuesday, October 29, 2013 5:06 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:49 AM
Points: 739, Visits: 2,470
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





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1509272
Posted Tuesday, October 29, 2013 4:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 1,730, Visits: 2,528

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)
I'm not fat, I'm gravity challenged.
Post #1509601
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse