?
Solved

Turning ASP code into a Stored Procedure

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

764 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