Solved

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

Posted on 2016-08-12
9
54 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Scott Pletcher 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 11

Expert Comment

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

Open in new window

1
 
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:Scott Pletcher
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

752 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