Solved

Turning ASP code into a Stored Procedure

Posted on 2013-01-24
2
263 Views
Last Modified: 2013-01-31
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
Comment
Question by:huerita37
[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
2 Comments
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 38817778
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
 
LVL 6

Accepted Solution

by:
esolve earned 500 total points
ID: 38819119
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

724 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