Problem figuring out how to do this

Question

Problem figuring out how to do this

MrBaseball34

SSCrazy

Points: 2515
More actions
November 24, 2008 at 4:12 pm

#124971

I need to get a balance into a select that is the sum of a column that is returned from a stored proc.
I've modified one of my other procedures to do this but am having a little trouble, see comments.
CREATE PROCEDURE [dbo].[rr_Collect_BalancesByPatient]
@MRN as varchar(25),
@BalType int = -1
AS
BEGIN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#PatBal') AND type in (N'U'))
DROP TABLE #PatBal
CREATE TABLE #PatBal(
[MRN] [varchar] (25) NULL,
[Amount] [decimal](18, 2) NULL,
[BalanceType] [int] NULL)
INSERT INTO #PatBal
SELECT MRN, SUM(OpenAmt) AS Amount,
1 AS BalanceType
FROM ptEncounter
WHERE (MRN = @MRN)
AND (LatestRec = 'True')
AND (DeletedByUserID IS NULL)
AND (OpenAmt > 0)
AND (BucketID < 4)
GROUP BY MRN
INSERT INTO #PatBal
SELECT MRN, SUM(OpenAmt) AS Amount,
2 AS BalanceType
FROM ptEncounter
WHERE (MRN = @MRN)
AND (LatestRec = 'True')
AND (DeletedByUserID IS NULL)
AND (OpenAmt > 0)
AND (BucketID = 4)
GROUP BY MRN
--
-- Would love to be able to pass a parameter to determine if I just want
-- the individual balances or all balances.
--
-- Trying the below code, I get an error:
-- Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
-- Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
--
DECLARE @sql varchar(max)
SET @sql = 'SELECT ' +
CASE
WHEN @BalType = 0 THEN
'SUM(Amount) FROM #PatBal WHERE BalanceType = 1'
WHEN @BalType = 1 THEN
'SUM(Amount) FROM #PatBal WHERE BalanceType = 0'
ELSE
'MRN, Amount, BalanceType FROM #PatBal'
END
EXEC sp_executeSQL @sql
DROP TABLE #PatBal
END
I also need to be able to return it from the original procedure like this:
-- This query returns data inside the rr_Collect_EncounterReport stored procedure
SELECT DISTINCT
ptSchedule.MRN AS MRN,
(exec rr_Collect_BalancesByPatient(ptSchedule.MRN, 0) AS PatientBalance,
(exec rr_Collect_BalancesByPatient(ptSchedule.MRN, 1) AS InsuranceBalance
FROM ptSchedule
INNER JOIN ptDemographics AS d ON d.MRN = ptSchedule.MRN
ORDER BY ptSchedule.MRN
How can I do this as I need to pass the MRN. I cannot make rr_Collect_BalancesByPatient into a function because it creates a temp table and uses a INSERT INTO #table call.
Can anyone help?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Have you read the article about running totals that I have linked in my signature block below? If not, you might want to spend some time reading it and working through the concepts presented.

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 2

While that was a very good article, it seems a little over my head. Is there no way to find out why I'm getting the error in the code I posted?

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 3

DOH!!!!

DECLARE @sql nvarchar(max)

.vs

DECLARE @sql varchar(max)

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 4

Ok, now that I have that part of it worked out. is there a way to do something like this?

CREATE FUNCTION rr_Get_BalanceByMRN

(

@MRN varchar(25),

@BalanceType int

)

RETURNS decimal

AS

BEGIN

RETURN exec rr_Collect_BalancesByPatient @MRN, @BalanceType

END

GO

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 5

Nevermind, we've decided to do it differently in the rr_Collect_EncounterReport procedure.

Thanks for taking a look.