Stored Procedure Problem - Couldn't find forum specifically for SP's

Stored Procedure Problem - Couldn't find forum specifically for SP's

• finlandrocks

  SELECT colList

  FROM (SELECT top 100 percent

  ii.it_id,

  ii.it_arrival_date + ii.it_nights HotelDepart,

  ii.fk_ut_unit_type AS HotelName,

  ii.it_arrival_date AS HotelArrival,

  ii.it_confirm_number AS HotelConfirmation,

  ii.it_nights AS HotelNights,

  ii.it_adults AS HotelAdult,

  ii.it_children AS HotelChild,

  ii.it_confirm_date AS ConfirmationDate

  FROM

  Booking

  JOIN CUSTOMER ON CUSTOMER.cus_id = Booking.fk_cus_id

  LEFT JOIN ITINERARY ii ON Booking.bk_id = ii.fk_bk_id

  LEFT JOIN VENDOR ven ON ven.ven_name = ii.fk_ven_name

  WHERE

  ven_itinerary = '1' AND it_cancel_date is null AND ven.fk_et_entity_type = 'Hotel' AND

  ii.it_arrival_date BETWEEN @StartDate AND @EndDate) as first

  JOIN (SELECT top 100 percent

  i.it_id AS TourNumber,

  Booking.bk_id AS BookingID,

  Booking.bk_issue_date AS BookingIssueDate,

  CUSTOMER.cus_last_name + ', ' + CUSTOMER.cus_first_name AS Customer1,

  CUSTOMER.cus_last_name2 + ', ' + CUSTOMER.cus_first_name2 AS Customer2,

  CUSTOMER.cus_home_ph AS CustomerPhone,

  i.it_arrival_date AS TourDate,

  i.it_confirm_number AS TourConfirmation,

  ven.ven_name AS VendorName,

  lms.sg_name AS SourceGroup,

  lmd.sd_name AS SourceDiv,

  lmss.src_name AS SourceName,

  i.it_arrival_date

  FROM

  Booking

  JOIN CUSTOMER ON CUSTOMER.cus_id = Booking.fk_cus_id

  JOIN lm_source_group lms ON customer.fk_sg_id = lms.sg_id

  JOIN lm_source lmss ON customer.fk_src_id = lmss.src_id

  JOIN lm_source_division lmd ON customer.fk_sd_id = lmd.sd_id

  LEFT JOIN ITINERARY i ON Booking.bk_id = i.fk_bk_id

  LEFT JOIN VENDOR ven ON ven.ven_name = i.fk_ven_name

  WHERE

  ven_itinerary = '1' AND it_cancel_date is null AND ven_name NOT LIKE '%Hotel%' AND

  i.it_arrival_date BETWEEN @StartDate AND @EndDate) as second

  ON first.it_id = second.it_id

  I get the error:

  Msg 156, Level 15, State 1, Procedure SUR_PreArrival_Revised, Line 44

  Incorrect syntax near the keyword 'SELECT'

  It is the SELECT beside colList

  It is like colList isn't a reserved name SQL understands. Is there a declare I should make or something

  Jeff

  
  

• Shaby

  NEVER MIND. Dumb me deleted the initial Stored Procedure so it couldn't ALTER.

  I changed ALTER to CREATE and it works AWESOME!!

  I can't thank you enough for your help. This opens many avenues previously deemed almost impossible very possible now.

  Again Thank you sooo much for your help!

  Jeff

  The post I just sent worked for me for your forum, once I changed the ALTER to CREATE.

  
  

• Andrew Conrad - MSFT

  It compiled fine in the master database but when running against my main table it gave me an error.

  Msg 205, Level 16, State 1, Procedure SUR_PreArrival_Revised, Line 19

  All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

  I'm not sure what this is referring to exactly with the code I sent.

  What does it mean and how, if possible, do I fix it

  Jeff

  
  

• Cameron Gocke

  There is also a Transact-SQL group for this sort of question...

  When I try to compile it I get the following error:

  Msg 156, Level 15, State 1, Procedure SUR_PREARRIVALREVISED, Line 82
  Incorrect syntax near the keyword 'WHERE'.
  

  This problem is because you have to name the derived table:

  AND VEN_NAME NOT LIKE '%Hotel%'
  AND I.IT_ARRIVAL_DATE BETWEEN @StartDate
  AND @EndDate)) as derivedTableName
  WHERE HOTELARRIVAL BETWEEN @StartDate
  AND @EndingDate

  The next error is:

  Msg 137, Level 15, State 2, Procedure SUR_PREARRIVALREVISED, Line 83
  Must declare the scalar variable "@EndingDate".
  

  There is no variable @endingDate, but ther is an enddate:

  AND VEN_NAME NOT LIKE '%Hotel%'
  AND I.IT_ARRIVAL_DATE BETWEEN @StartDate
  AND @EndDate)) as derivedTableName
  WHERE HOTELARRIVAL BETWEEN @StartDate
  AND @EndDate

  Now it compiles.

  
  
  

• palmsey

  Yes one row for the first select and one for the second select.

  Everyone is leaving for the weekend, so I will try this and post once I see if it works.

  Thank you very much for your time.

  I will post Monday.

  Jeff

  
  

• Erik Read

  Is there one row in the first select and one row in the second for each customer

  If so then do a join instead of a UNION, something like:

  SELECT collist
  FROM (select <first query>) as first
  JOIN (select <second query>) as second
  ON first.commonColumn = second.commonColumn

  Will this work

  
  
  

• Daron

  Where I work owns many resorts and hotels. When they sell a package and set dates for the hotel stay, they also set dates for a tour of the resort. If you notice, as example, that "it_arrival_date" and "it_confirm_number" is listed in both statements, once for the Hotel Leg and one for the Tour Leg. The field "it_arrival_date" and "it_confirm_number" in the table "itinerary" holds the information for each leg. So each itinerary id has two records for each client. One for the Tour and one for the Hotel. The only way to distinguish the two is through the WHERE statements. The entity types separates the legs. So I must get "it_confirm_number" with entity type Hotel and the "it_confirm_number" from the Tour leg to get all the information I need for the reports from the same table. I didn't design it but I have to work with it :-(

  Since each it_id or bk_id will return two records the stored procedure gets tricky to make sure I get all the information in one procedure. I am required to export an XML file (one file) that populates a report that is tied to that XML file. That is why I can't use two stored procedures since I have no way of merging two files to assure data accuracy with the structure of the software. That is why I was trying to have two SELECT statements that would populate the alias names based on the WHERE statements for the main SELECT statement to pull from.

  Any ideas

  Jeff

  
  

• Jimmy Li

  ALTER PROCEDURE [dbo].[SUR_PreArrival_Revised]

  @StartDate DateTime,

  @EndDate DateTime

  AS

  BEGIN

  SET NOCOUNT ON;

  SELECT <------THIS IS WHERE THE ERROR TAKES ME

  derivedTour.Customer1,

  derivedTour.Customer2,

  derivedTour.CustomerPhone,

  derivedTour.TourConfirmation,

  convert(varchar(15), derivedHotel.ConfirmationDate, 101) AS ConfirmationDate,

  convert(varchar(15), derivedTour.BookingIssueDate,101) AS BookingIssueDate,

  derivedTour.BookingID,

  derivedTour.VendorName,

  derivedTour.TourDate,

  derivedTour.TourNumber,

  derivedTour.SourceGroup,

  derivedTour.SourceDiv,

  derivedTour.SourceName,

  derivedHotel.HotelName,

  convert(varchar(15), derivedHotel.HotelArrival, 101) as HotelArrival,

  convert(varchar(15), derivedHotel.HotelDepart, 101) as HotelDepart,

  derivedHotel.HotelNights,

  derivedHotel.HotelConfirmation,

  derivedHotel.HotelAdult,

  derivedHotel.HotelChild

  FROM

  (SELECT top 100 percent

  Booking.bk_id,

  ii.it_arrival_date + ii.it_nights HotelDepart,

  ii.fk_ut_unit_type AS HotelName,

  ii.it_arrival_date AS HotelArrival,

  ii.it_confirm_number AS HotelConfirmation,

  ii.it_nights AS HotelNights,

  ii.it_adults AS HotelAdult,

  ii.it_children AS HotelChild,

  ii.it_confirm_date AS ConfirmationDate

  FROM

  Booking

  JOIN CUSTOMER ON CUSTOMER.cus_id = Booking.fk_cus_id

  LEFT JOIN ITINERARY ii ON Booking.bk_id = ii.fk_bk_id

  LEFT JOIN VENDOR ven ON ven.ven_name = ii.fk_ven_name

  WHERE

  ven.ven_itinerary = '1' AND ii.it_cancel_date is null AND ven.fk_et_entity_type = 'Hotel' AND

  ii.it_arrival_date BETWEEN @StartDate AND @EndDate) as derivedHotel

  JOIN

  (SELECT top 100 percent

  Booking.bk_id AS BookingID,

  Booking.bk_issue_date AS BookingIssueDate,

  CUSTOMER.cus_last_name + ', ' + CUSTOMER.cus_first_name AS Customer1,

  CUSTOMER.cus_last_name2 + ', ' + CUSTOMER.cus_first_name2 AS Customer2,

  CUSTOMER.cus_home_ph AS CustomerPhone,

  i.it_arrival_date AS TourDate,

  i.it_id AS TourNumber,

  i.it_confirm_number AS TourConfirmation,

  vend.ven_name AS VendorName,

  lms.sg_name AS SourceGroup,

  lmd.sd_name AS SourceDiv,

  lmss.src_name AS SourceName,

  i.it_arrival_date

  FROM

  Booking

  JOIN CUSTOMER ON CUSTOMER.cus_id = Booking.fk_cus_id

  JOIN lm_source_group lms ON customer.fk_sg_id = lms.sg_id

  JOIN lm_source lmss ON customer.fk_src_id = lmss.src_id

  JOIN lm_source_division lmd ON customer.fk_sd_id = lmd.sd_id

  LEFT JOIN ITINERARY i ON Booking.bk_id = i.fk_bk_id

  LEFT JOIN VENDOR vend ON vend.ven_name = i.fk_ven_name

  WHERE

  ven_itinerary = '1' AND it_cancel_date is null AND ven_name NOT LIKE '%Hotel%' AND

  i.it_arrival_date BETWEEN @StartDate AND @EndDate) as derivedTour

  ON derivedHotel.bk_id = derivedTour.BookingID

  WHERE

  HotelArrival BETWEEN @StartDate AND @EndDate

  ORDER BY

  HotelArrival

  END

  I get the following error.

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near 'SELECT'.

  I thought I had it but.... I really need more SQL training in Stored Procedures :-(

  Jeff

  
  

• Tariq Abu Dayyeh

  Actually colList was just shorthand for you typing out:

  first.<columnName>, first.<nextColumnName>, second.<columnName>

  Sorry for that confusion. Otherwise, on sql server 2005 this statement compiles just fine for me.. Maybe post the entire procedure

  
  
  

• CHJ3

  The results of your queries that you are unioning must match perfectly. Currently yours don't match, in fact I don't see a lot of overlap:

  ((SELECT top 100 percent

  II.IT_ARRIVAL_DATE + II.IT_NIGHTS HOTELDEPART,
  II.FK_UT_UNIT_TYPE AS HOTELNAME,
  II.IT_ARRIVAL_DATE AS HOTELARRIVAL,
  II.IT_CONFIRM_NUMBER AS HOTELCONFIRMATION,
  II.IT_NIGHTS AS HOTELNIGHTS,
  II.IT_ADULTS AS HOTELADULT,
  II.IT_CHILDREN AS HOTELCHILD,
  II.IT_CONFIRM_DATE AS CONFIRMATIONDATE
  

  UNION ALL
  (SELECT top 100 percent

  BOOKING.BK_ID AS BOOKINGID,
  BOOKING.BK_ISSUE_DATE AS BOOKINGISSUEDATE,
  CUSTOMER.CUS_LAST_NAME + ', ' + CUSTOMER.CUS_FIRST_NAME AS CUSTOMER1,
  CUSTOMER.CUS_LAST_NAME2 + ', ' + CUSTOMER.CUS_FIRST_NAME2 AS CUSTOMER2,
  CUSTOMER.CUS_HOME_PH AS CUSTOMERPHONE,
  I.IT_ARRIVAL_DATE AS TOURDATE,
  I.IT_ID AS TOURNUMBER,
  I.IT_CONFIRM_NUMBER AS TOURCONFIRMATION,
  VEN.VEN_NAME AS VENDORNAME,
  LMS.SG_NAME AS SOURCEGROUP,
  LMD.SD_NAME AS SOURCEDIV,
  LMSS.SRC_NAME AS SOURCENAME,
  I.IT_ARRIVAL_DATE
  

  What are you trying to do exactly

  
  
  
• Stored Procedure Problem - Couldn't find forum specifically for SP's