TSQL72 Create a stored procedure that accepts a number of se

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

Solution

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