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 > 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 [/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

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • 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 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