[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Turning ASP code into a Stored Procedure

How do I turn the following into a stored procedure?

Here is what I have so far:

ALTER PROCEDURE [dbo].[get_PayorSequence]
    @agency_id INTEGER,
    @clientId INTEGER,
    @epcccId INTEGER,
    @ServiceDate DATETIME
AS
BEGIN

    SET NOCOUNT ON
    
    DECLARE @sql VARCHAR(2000),
        @PayorName Varchar(50),
        @Priority Int,
        @Bill_Medical Int,
        @bMedicare Int,
        @bPrivateIns Int,
        @bSelfPay  Int,
        @bCounty Int
  
    BEGIN

		SET @sql = SELECT @PayorName = PayorName, @Priority = ISNULL(Payor.Priority, 0) as Priority, @Bill_Medical = ISNULL(Bill_Medical, 0) as Bill_Medical, 
		@bMedicare = ISNULL(bMedicare, 0) as bMedicare, 
		@bPrivateIns = ISNULL(bPrivateIns, 0) as bPrivateIns, @bSelfPay = ISNULL(bSelfPay, 0) as bSelfPay 
		FROM payor 
		INNER JOIN PayorInfo on PayorInfo.ID = Payor.PayorInfoID 
		WHERE Payor.AgencyID = @agency_id AND ClientID = @clientId 
		AND payor.Id IN (SELECT Id FROM Payor WHERE pyEpcccId = @epcccId
		and ((cast('" & claims("serviceDate") & "' as datetime) between CAST(eligibilityStart as datetime) and coalesce(eligibilityEnd, getDate()) 
		 ))) ORDER BY Payor.Priority 
		 
		 
      print(@sql)
      exec(@sql)
    END

Open in new window


And the below is what I need to add

do while not rsPayor.eof
    SELECT CASE rsPayor("Priority")          
        CASE 0
            If rsPayor("Bill_Medical") Then
                sMedical = rsPayor("PayorName")  & "<BR>"
            ElseIf rsPayor("bMedicare") Then 
                sMedicare = rsPayor("PayorName")  & "<BR>"
            ElseIf rsPayor("bSelfPay") Then 
                sSelfPay = rsPayor("PayorName")  & "<BR>"
            ElseIf rsPayor("bPrivateIns") Then 
                sPrivateIns = rsPayor("PayorName")  & "<BR>"
            Else 
                sCounty = rsPayor("PayorName")  & "<BR>"
            End If
        CASE 1
            sPrimary = "P: " & rsPayor("PayorName")  & "<BR>"
        CASE 2
            sSecondary = "S: " & rsPayor("PayorName")  & "<BR>"
        CASE 3
            sTeritary = "T: " & rsPayor("PayorName")  & "<BR>"  
        CASE Else
            sPayorSequence = sPayorSequence & rsPayor("Priority") & ": " & rsPayor("PayorName") & "<BR>"
    End SELECT
		
	rsPayor.movenext
loop

sMultiPayor = sPrimary & sSecondary & sTeritary & sPayorSequence & sSelfPay & sPrivateIns & sMedicare & sMedical & sCounty

Open in new window

0
huerita37
Asked:
huerita37
1 Solution
 
Luis PérezSoftware Architect in .NetCommented:
What you need is a CURSOR. Take a look on that:
http://msdn.microsoft.com/en-us/library/ms180169(v=sql.110).aspx

The sequence is as follows:

1. Declare the variables in which you will store the retrieved values
DECLARE @id_person INT
DECLARE @name VARCHAR(50)

2. Declare and open the cursor
DECLARE myCursor CURSOR FOR
    SELECT ID_PERSON, NAME FROM PERSONS
OPEN myCursor

3. Get the first record
FETCH NEXT FROM myCursor INTO @id_person, @name

4. Iterate thru all records
WHILE @@FETCH_STATUS = 0
BEGIN
    --Your code here, this code will execute 1 time for each record retrieved in the
    --SELECT clause on the cursor declaration

    --At the end of the WHILE, get the next record
    FETCH NEXT FROM myCursor INTO @id_person, @name
END

5. Close the cursor and free memory
CLOSE myCursor
DEALLOCATE myCursor

Hope that helps.
0
 
esolveCommented:
This will get you started. I am not sure what your end result should look like



DECLARE @TmpTable TABLE (ID INT IDENTITY(1,1),PayorName varchar(50),[Priority] int,Bill_Medical int,bMedicare int,bPrivateIns int,bSelfPay int,bCounty int)

--in stead of having two inserts use your own table to fill table variable (this is just for example)
INSERT INTO @TmpTable (PayorName,[Priority],Bill_Medical,bMedicare,bPrivateIns,bSelfPay,bCounty)
SELECT 'James',1,1,1,1,1,1

INSERT INTO @TmpTable (PayorName,[Priority],Bill_Medical,bMedicare,bPrivateIns,bSelfPay,bCounty)
SELECT 'Pete',2,2,2,2,2,2

--select * from @TmpTable


DECLARE @Result NVARCHAR(MAX)
SET @Result = ''

DECLARE @PayorName NVARCHAR(100)
DECLARE @Priority INT
DECLARE @Bill_Medical INT
DECLARE @bMedicare BIT --looks like it should be a bit (0or1)
DECLARE @IteratorID INT
SET @IteratorID = 0

WHILE (@IteratorID IS NOT NULL)
BEGIN
      --RETRIEVE ID OF TOP ROW NOT YET DELETED
      SET @IteratorID = (SELECT TOP 1 ID FROM @TmpTable)
      
      --RETRIEVE PRIORITY FOR THIS ROW (and other values below)
      SET @Priority = (SELECT [Priority] FROM @TmpTable WHERE ID = @IteratorID)
      SET @PayorName = (SELECT PayorName FROM @TmpTable WHERE ID = @IteratorID)
      
      IF(@Priority IS NOT NULL)
      BEGIN
            IF(@Priority = 0) BEGIN      SET @Result = @Result + '1' END
            ELSE IF(@Priority = 1) BEGIN      SET @Result = @Result + 'P:' + @PayorName END
            ELSE IF(@Priority = 2) BEGIN      SET @Result = @Result + 'S:' + @PayorName  END
            ELSE IF(@Priority = 3) BEGIN      SET @Result = @Result + 'T:' + @PayorName  END
            ELSE BEGIN SET @Result = @Result + 'what you want' END
      END
            
      --IMPORTANT MUST DELETE THE ROW TO PREVENT STACK OVERFLOW
      DELETE FROM @TmpTable WHERE ID = @IteratorID
END


SELECT @Result
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now