Solved

Turning ASP code into a Stored Procedure

Posted on 2013-01-24
2
258 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
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now