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

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
danielolorenzAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
Russ SuterCommented:
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
 
Russ SuterCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Craig YellickDatabase ArchitectCommented:
... AND ( LAST_STATUS IN (@CLAIM_STATUS) or @CLAIM_STATUS='ALL' ) 

Open in new window

1
 
Russ SuterCommented:
@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
 
Scott PletcherSenior DBACommented:
@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
 
danielolorenzAuthor Commented:
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
 
Russ SuterCommented:
Scott's answer is still better. Using a CASE statement in a WHERE clause defeats indexing which can adversely affect performance.
0
 
danielolorenzAuthor Commented:
Wow great solution.  I did not know you could do it that way
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.