asked on

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

Russ Suter

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).

Russ Suter

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.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Craig Yellick

... AND ( LAST_STATUS IN (@CLAIM_STATUS) or @CLAIM_STATUS='ALL' )

Open in new window

Russ Suter

@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) :)

Scott Pletcher

@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.

danielolorenz

ASKER

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

Russ Suter

Scott's answer is still better. Using a CASE statement in a WHERE clause defeats indexing which can adversely affect performance.

danielolorenz

ASKER

Wow great solution. I did not know you could do it that way