Can this SQL Server stored procedure be turned into a view?

This procedure populates a temporary table with hotel night rates for the duration of a guest stay. If the rate is the same for all nights the data comes from the primary booking table. If there are different rates for different nights, the rates come from another table. Can this be done in a view rather than executing the stored procedure then selecting from the temporary table?

Thanks.

CREATE PROCEDURE RECALC_PROFORMER_GUESTVIEW @GUEST_SEQNO INT
AS
DECLARE @BK_ARRIVE_DATE DATETIME;
DECLARE @BK_LEAVE_DATE DATETIME;
DECLARE @BK_ROOMRATE FLOAT;
DECLARE @ARRIVEDATE DATETIME;
DECLARE @LEAVEDATE DATETIME;
DECLARE @CURRENTDATE DATETIME;
DECLARE @DAYSRATE FLOAT;
BEGIN
  DELETE FROM
    PS_TMP_PROFORMER_GUESTVIEW;

  /*Get values for guest*/
  SELECT @BK_ARRIVE_DATE = BK_ARRIVE_DATE,
         @BK_LEAVE_DATE = BK_LEAVE_DATE,
         @BK_ROOMRATE = BK_ROOMRATE
    FROM PS_GUEST_ACC
   WHERE (SEQNO = @GUEST_SEQNO);

  /*Round figures to midnight of the day*/
  SET @ARRIVEDATE = DATEADD(DAY, DATEDIFF(DAY, 0, @BK_ARRIVE_DATE), 0);
  SET @LEAVEDATE = DATEADD(DAY, DATEDIFF(DAY, 0, @BK_LEAVE_DATE), 0);
  SET @CURRENTDATE = @ARRIVEDATE;

  WHILE (@CURRENTDATE < @LEAVEDATE)
  BEGIN
    SET @DAYSRATE = null;
    SELECT @DAYSRATE = ROOMRATE
      FROM PS_GUEST_RATES
     WHERE ((GUEST_SEQNO = @GUEST_SEQNO) and (PS_DATE = @CURRENTDATE));

    IF (@DAYSRATE IS NULL)
    BEGIN
      SET @DAYSRATE = @BK_ROOMRATE;
    END

    INSERT INTO PS_TMP_PROFORMER_GUESTVIEW
        (GUEST_SEQNO, PS_DATE, ROOMRATE)
      VALUES
        (@GUEST_SEQNO, @CURRENTDATE, @DAYSRATE);

    SET @CURRENTDATE = DATEADD(DAY, 1, @CURRENTDATE);
  END
END
monstercodeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
The constraint here is that you might not have the necessary data in table form to move this to a view. This stored proc generates one record per day for each day between @ARRIVEDATE & @LEAVEDATE. If you have a calendar table, or a table of integers, then it can be used to supply the dates in place of the begin loop.

{+ edit}
I would suggest you provide some sample data from the tables you do have, and then an "expected result" from that data.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Without sample data will be hard to give you an assertive answer.
If you facing performance issues you can always replace the DELETE FROM with TRUNCATE.
Scott PletcherSenior DBACommented:
I suggest a table-valued function.  This can be joined into your query just like a view would be, but the tvf can filter data much sooner in the process than a view.

To invoke the tvf:
SELECT *
FROM dbo.RECALC_PROFORMER_GUESTVIEW_NEW(<guest_seqno>)

Or, using another table to provide the guest_seqno:
SELECT dt.col1, dt.col2, ..., rpg.ROOMRATE, ...
FROM data_table dt
CROSS APPLY dbo.RECALC_PROFORMER_GUESTVIEW_NEW(dt.GUEST_SEQNO) AS rpg



CREATE FUNCTION RECALC_PROFORMER_GUESTVIEW_NEW
(
    @GUEST_SEQNO int
)
RETURNS TABLE
AS
RETURN (
    SELECT @GUEST_SEQNO AS GUEST_SEQNO, CURRENTDATE AS PS_DATE, COALESCE(PGR.ROOMRATE, PGA.BK_ROOMRATE) AS ROOMRATE
    FROM (
        SELECT 
              DATEADD(DAY, DATEDIFF(DAY, 0, BK_ARRIVE_DATE), 0) AS ARRIVEDATE,
              DATEADD(DAY, DATEDIFF(DAY, 0, BK_LEAVE_DATE), 0) AS LEAVEDATE,
              BK_ROOMRATE
        FROM PS_GUEST_ACC
        WHERE (SEQNO = @GUEST_SEQNO)
    ) AS PGA
    INNER JOIN tally t ON /*"standard" tally table, code to create it below*/
        t.tally BETWEEN 0 AND DATEDIFF(DAY, PGA.ARRIVEDATE, PGA.LEAVEDATE)
    CROSS JOIN (
        SELECT DATEADD(DAY, t.tally, PGA.ARRIVEDATE) AS CURRENTDATE
    ) AS assign_alias_names
    LEFT OUTER JOIN PS_GUEST_RATES PGR ON
        PGR.GUEST_SEQNO = @GUEST_SEQNO AND
        PGR.PS_DATE = CURRENTDATE
)
GO --end of function

Open in new window


--code to create the tally table
CREATE TABLE dbo.tally (
    tally int NOT NULL,
    CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( tally )
    )
INSERT INTO dbo.tally VALUES(0);

WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
    SELECT 1 AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT 1 AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
)
INSERT INTO dbo.tally
SELECT tally
FROM cteTally1Mil
ORDER BY tally

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
please note that if you already have a table of integers that can be used instead of the table dbo.tally that Scott has proposed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.