?
Solved

Turning ASP code into a Stored Procedure

Posted on 2013-01-24
2
Medium Priority
?
266 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

649 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