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 storedprocedure Expand / Collapse
Author
Message
Posted Friday, November 1, 2013 11:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:36 AM
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)
Post #1510847
Posted Sunday, November 3, 2013 12:46 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:26 PM
Points: 880, Visits: 608
I really have no idea what you are asking, since your procedure doesn't reference the tables you mention at all.

Do you mean you want to set the amount variable? Or are you trying to update one or the other of the tables you mentioned with the value you calculate?

In general, when posting a question, you'll get much better quality answers if you put in the time to create a script that sets up the problem, and give an example of the result you want to see based on the sample data that you provide.




Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #1510925
Posted Monday, November 4, 2013 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
Please keep to one thread for all questions. There are at least three for this one.

Here is my response from one of the others. Let's use this on as the "main" thread.

Sean Lange (11/4/2013)
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
)
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)
Post #1511139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse