Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Regarding storedprocedure


Regarding storedprocedure

Author
Message
p.avinash689
p.avinash689
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 86
I have written a stored procedure like
USE [nxnv1_temp]
GO
/****** Object: StoredProcedure [dbo].[SP_CONSULTATION_DETAILS1] Script Date: 11/02/2013 10:06:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CONSULTATION_DETAILS1]
@AGE numeric(3, 0),
@date DATE ,
@time TIME,
@SEX varchar(1),
@AMOUNT numeric(6, 2)
AS
BEGIN
SELECT AMOUNT=
          (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 > '18: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) FROM MASTEROPCONSAMT
END

i want to set the values for amount field. is it need to join the two tables? please suggest me or help me to close this issue

i have a tables like below
registration table:-

PRNO   numeric(11, 0)   (primary key)
REGFROM   varchar(10)   
REGDATE   datetime   
FIRSTNAME   varchar(60)   
RELATIONCODE   varchar(3)   
GUARDIANNAME   varchar(50)   
DOB   datetime   
AGE   numeric(3, 0)   
AGETYPE   varchar(6)   
SEX   varchar(1)   
ORGCODE   varchar(10)   
BGROUP   varchar(3)   
RHTYPE   varchar(3)   
HNO   varchar(50)   
STREET   varchar(50)   
LOC   varchar(50)   
AREACODE   numeric(6, 0)   
PHONER   varchar(15)   
PHONEO   varchar(15)   
PAGER   varchar(15)   
MOBILE   varchar(15)   
FAX   varchar(15)
EMAIL   varchar(50)   
IDENTIFICATIONMARKS   varchar(200)   
REFDOCTCODE   varchar(10)   
PAYTYPE   varchar(11)   
USERID   varchar(50)   
SENT   nchar(1)   
REMARKS   varchar(50)
ReligionCode   numeric(2, 0)   
MIDDLENAME   varchar(20)
LASTNAME   varchar(30)   
TITLE   varchar(10)   

and

opconsamt table:-

DOCCD   varchar(6)   
CONSTYPE   varchar(3)   
TARIFFCD   varchar(10)   
VISITNO   numeric(2, 0)   
AMOUNT   numeric(6, 2)   
VALIDDAYS   numeric(3, 0)   
MAXVISITS   numeric(2, 0)   
COMPCODE   varchar(10)   
FOLLOWUPAMT   numeric(6, 2)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16510 Visits: 16985
I started by formatting your proc so we can read it. If you post this stuff in a code block (using IFCode shortcuts on the left when posting) it will help maintain your formatting.




ALTER PROCEDURE [dbo].[SP_CONSULTATION_DETAILS1] @AGE NUMERIC(3, 0)
   ,@date DATE
   ,@time TIME
   ,@SEX VARCHAR(1)
   ,@AMOUNT NUMERIC(6, 2)
AS
BEGIN
   SELECT AMOUNT = (
         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 > '18: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
         Wink
   FROM MASTEROPCONSAMT
END




It is impossible to determine what you are trying to do here. The biggest issue I see is that your stored proc is not filtering which row from MASTEROPCONSAMT. It is going to set the value for Amount for only 1 row in your table. If could post ddl, sample data and desired output for the two tables we can help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16510 Visits: 16985
Duplicate post. Direct replies here. http://www.sqlservercentral.com/Forums/Topic1510847-392-1.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search