Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-08-12
9
Medium Priority
?
65 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 11

Expert Comment

by:Craig Yellick
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 70

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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