T-SQL-7-2 Create a stored procedure that accepts a number of seats as a paramete
ID: 3924566 • Letter: T
Question
T-SQL-7-2
Create a stored procedure that accepts a number of seats as a parameter and lists the table ID, area ID, and area name for all tables that have at least that many seats. Print an error message if there are no tables with that number of seats.
DATABASE (key fields are underlined)
EMPLOYEE TABLE: EMP_ID, EMP_LNAME, EMP_STREET, EMP_CITY, EMP_STATE, EMP_ZIP, EMP_START_DATE
TABLES TABLE: TABLE_ID, AREA_ID, TABLE_SEATS
AREA TABLE: AREA_ID, AREA_NAME, AREA_SUPERVISOR_EMPLOYEE_ID
CUSTOMER TABLE: CUST_ID, CUST_LAST_NAME, CUST_NUMBER_OF_GUESTS
ASSIGNMENT TABLE: EMP_ID, TABLE_ID
SEATING TABLE: CUST_ID, TABLE_ID, SEATING_COST, SEATING_DATE, SEATING_TIP
Explanation / Answer
Create procedure database_name.table_vacancy @no_of_seats int NOT NULL
As
Begin
IF EXISTS (SELECT TABLE_ID,AREA.AREA_ID,AREA_NAME FROM TABLES,AREA where TABLES.TABLE_ID = AREA.AREA_ID and TABLE_SEATS >=@no_of_seats;)
BIGIN
SELECT TABLE_ID,AREA.AREA_ID,AREA_NAME FROM TABLES,AREA where TABLES.TABLE_ID = AREA.AREA_ID and TABLE_SEATS >=@no_of_seats;
END
ELSE
BIGIN
RAISERROR ('There is no table availabe with specified vacancy.');
END
End
Go
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.