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


Store Procedure Requirement extraction


Store Procedure Requirement extraction

Author
Message
hoseam
hoseam
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 427
Hi

I have create a store procude with Application Id as Input parameter, and make few select statement on my store procedure.

My problem is with the two below statements, in terms of what I need to do. I'm struggling with Requirements extraction.

Please help.


1.Find Application Id of related ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application:
Select S_OPTY.NAME where S_OPTY.PAR_OPTY_ID = [Original Application Id] and S_OPTY.X_SALES_METHOD_ID identifies S_SALES_METHOD.NAME = either ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’.

2.Select from PR_MONEY_BFN:
Select record from PR_MONEY_BFN where:
PR_MONEY_BFN.APPLICATION_NO = S_OPTY.NAME identified above (ie for the actual ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application); and
PR_MONEY_BFN.BUS_FUNC_CLASS = ‘NTUT’ or ‘CANT’; and
PR_MONEY_BFN.BUS_FUNC_STATUS = 80.
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
which bit are you struggling with?

working backwards...

Can you create the second query with a variable
Can you create the second query with a fixed value.

Can you assign a value to the variable based on the first query

Can you validate the result of the first query

can you exit the procedure if the value from the first query is in error

can you define and pass the parameter to the stored procedure
hoseam
hoseam
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 427
Below is my store proc, all the information above has to be part of my where clause according to the requirements.

ALTER PROCEDURE [dbo].[prc_TopUpCancellation_xml]

@ApplicationID VARCHAR(10)

AS

SELECT

      Application_Id
,      Policy_Id
,      Date_of_Disinvestment
,      Original_Investment_Value
,      Payout_Value

NB. now there from and where clause I have to extract it from that given Info
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
1.Find Application Id of related ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application:
Select S_OPTY.NAME where S_OPTY.PAR_OPTY_ID = [Original Application Id] and S_OPTY.X_SALES_METHOD_ID identifies S_SALES_METHOD.NAME = either ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’.

2.Select from PR_MONEY_BFN:
Select record from PR_MONEY_BFN where:
PR_MONEY_BFN.APPLICATION_NO = S_OPTY.NAME identified above (ie for the actual ‘Cancellation - Top Ups’ or ‘Not Taken Up - Top Ups’ application); and
PR_MONEY_BFN.BUS_FUNC_CLASS = ‘NTUT’ or ‘CANT’; and
PR_MONEY_BFN.BUS_FUNC_STATUS = 80.

Your requirement is not clear. You have two fields called NAME; one on S_OPTY and one on S_SALES_METHOD. in requirement 2 You have said you want the one from S_OPTY (which you already have)


Select
M.*,
O.*

FROM
S_OPTY O
JOIN
PR_MONEY_BFN M on M.APLLICATION_NO = O.NAME
WHERE
O.PAR_OPTY_ID = @ApplicationID



if you want the one from S_SALES_METHOD



Select
M.*,
O.*

FROM
S_OPTY O
JOIN
S_SALES_METHOD S on S.X_SALES_METHOD_ID = O.X_SALES_METHOD_ID
PR_MONEY_BFN M on M.APLLICATION_NO = O.NAME
WHERE
O.PAR_OPTY_ID = @ApplicationID


I am making the assumption that the join field is called X_SALES_METHOD_ID in both tables
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