Solved

I Have a Stored Procedure that needs to Search for Individual Parameters and the Search for ALL

Posted on 2016-08-12
9
37 Views
Last Modified: 2016-08-12
I have a stored procedure that searches for a parameter @CLAIM_STATUS.  The stored procedure searches for an open claim status and then a closed claim status.  I need the stored procedure to search for open and closes claims when ALL is selected.  How is this done?

SQL line of code search for claim status:
AND LAST_STATUS IN (@CLAIM_STATUS)

 
ALTER PROCEDURE [dbo].[sp_LossRunSummaryClaimantList]
	@SECURITY_ID AS VARCHAR(50), @ACCOUNT_NBR AS VARCHAR(40), @UNDERWRITING_PROGRAM_NBR AS VARCHAR(50), @CLAIM_STATUS AS VARCHAR(50), @LOB_KEY AS VARCHAR(1000), @POLICY_YR AS VARCHAR(1000)  --@POLICY_YR AS INT
AS
BEGIN

		SELECT
				LineOfBusiness = LOB_NM,
				PolicyYear = POLICY_YR,
				ClaimStatus = CLAIM_LAST_STATUS,
				ClaimNumber = ISNULL(CLAIM_LEGACY_NBR, CLAIM_NBR),
				ClaimantNumber = CLAIMANT_NBR,
				ClaimantName = CLAIMANT_NM,
				LossDate = LOSS_DT,
				Paid = SUM(GROSS_LOSS_PAID + GROSS_EXPENSE_PAID) + SUM(WC_GROSS_PAID) ,
				Reserves = SUM(LOSS_RESERVE + EXPENSE_RESERVE) + SUM(WC_RESERVE) ,
				TotalIncurredAmount = SUM(GROSS_LOSS_PAID + GROSS_EXPENSE_PAID + LOSS_RESERVE + EXPENSE_RESERVE + SUBROGATION + SALVAGE + DEDUCTIBLE_RECOVERY + RECOVERIES_OTHER) +
				SUM(WC_GROSS_PAID + WC_RESERVE + WC_RECOVERIES),
				Recoveries = SUM(SUBROGATION + SALVAGE + DEDUCTIBLE_RECOVERY + RECOVERIES_OTHER) + SUM(WC_RECOVERIES)
		FROM dbo.AGG_MEMBER_CENTER
		WHERE ACCOUNT_NBR = @ACCOUNT_NBR
			 AND UNDERWRITING_PROGRAM_NBR = @UNDERWRITING_PROGRAM_NBR
			 AND LAST_STATUS IN (@CLAIM_STATUS) 

Open in new window


Thanks,

Dan
0
Comment
Question by:danielolorenz
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41754146
How difficult is it to alter the CLAIM_STATUS column to be an INT? The reason I'm asking is because you can store the value as a binary multiple then use bit masking to retrieve desired values. It's a very useful tool. You can easily create a simple linked table that has the CLAIM_STATUS_ID and CLAIM_STATUS_NAME for readability. Take the following example. Assume I have this as a table storing all possible claim statuses:
CLAIM_STATUS_ID    CLAIM_STATUS_NAME
1                  Open
2                  Assigned
4                  Waiting
8                  Closed

Open in new window

Now, for my actual data table I might have a table definition like this:
CREATE TABLE [dbo].[CLAIMSs](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Title] [VARCHAR](50) NULL,
	[Description] [VARCHAR](MAX) NULL,
	[RequestDate] [DATETIME] NULL,
	[Claim_Status_Id] [INT] NULL,
 CONSTRAINT [PK_CLAIMSS] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

I can then do the following:
DECLARE @CLAIM_STATUS INT = 15

SELECT
	*
FROM
	[dbo].[CLAIMS]
WHERE
	([CLAIMS].[Claim_Status_Id] & @CLAIM_STATUS) <> 0

Open in new window

By using binary multiples you can select any combination of statuses by simply adding the values together. For example, If you want everything except for closed you'd just use @CLAIM_STATUS = 7 (1 + 2 + 4).
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41754154
If you really need to leave it as text you basically have 2 options that I can think of:

1. Add a conditional clause in your stored procedure so that if @CLAIM_STATUS = "ALL" then you run a completely different query that doesn't include the WHERE clause.

2. Create a table in memory and populate it based on the passed-in value of @CLAIM_STATUS. Then modify your query so your WHERE clause looks more like AND LAST_STATUS IN (SELECT * FROM @STATUS_VALUES) where @STATUS_VALUES is a table variable populated from a query like
SELECT DISTINCT LAST_STATUS FROM AGG_MEMBER_CENTER

Open in new window


I'd prefer the 2nd approach since it's easier to read and maintain. Performance should be about the same for either approach.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 41754175
Just change the WHERE clause, like so:

            WHERE ACCOUNT_NBR = @ACCOUNT_NBR
                   AND UNDERWRITING_PROGRAM_NBR = @UNDERWRITING_PROGRAM_NBR
                   AND (@CLAIM_STATUS = 'ALL' OR LAST_STATUS IN (@CLAIM_STATUS))
1
 
LVL 11

Expert Comment

by:CraigYellick
ID: 41754181
... AND ( LAST_STATUS IN (@CLAIM_STATUS) or @CLAIM_STATUS='ALL' ) 

Open in new window

1
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 20

Expert Comment

by:Russ Suter
ID: 41754220
@ScottPletcher

Ever had a situation where you overthink a very simple problem? DOH!

@CraigYellick

How is what you wrote ANY different from what Scott posted?

@danielolorenz

Go with Scott's answer. (Unless you enjoy making lots of extra work for yourself then mine is a better choice) :)
1
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41754224
@Russ:

I suspect Craig hadn't seen my post yet, as the times are very close. A typical cross-post -- I've done it many times here myself.
0
 

Author Comment

by:danielolorenz
ID: 41754232
Here is my solution:

WHERE ACCOUNT_NBR = @ACCOUNT_NBR
                   AND UNDERWRITING_PROGRAM_NBR = @UNDERWRITING_PROGRAM_NBR
                   AND LAST_STATUS = CASE
                                                WHEN @CLAIM_STATUS = 'ALL'
                                                      THEN LAST_STATUS
                                                ELSE @CLAIM_STATUS
                                          END

When the 'ALL' parameter is passed in the stored procedure them all of the data in the LAST_STATUS column is displayed
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41754234
Scott's answer is still better. Using a CASE statement in a WHERE clause defeats indexing which can adversely affect performance.
0
 

Author Closing Comment

by:danielolorenz
ID: 41754246
Wow great solution.  I did not know you could do it that way
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now