0JEmt698

1-- Stored procedure 1 (Вывод истории выдачи книги по ее названию)
2CREATE PROCEDURE [dbo].Proc_GetBorrowingsHistory
3	@BOOK_TITLE nvarchar(100)
4AS
5BEGIN
6	-- Объявление табличной переменной @borrowing
7	DECLARE @borrowing TABLE (title nvarchar(100), memberid int, borrowdate date, returndate date)
8	INSERT INTO @borrowing (title, memberid, borrowdate, returndate)
9		SELECT b.title, br.memberid, br.borrowdate, br.returndate
10		FROM [dbo].[Books] b INNER JOIN [dbo].Borrowings br
11		 ON b.bookid = br.bookid
12		WHERE b.title = @BOOK_TITLE
13	-- Проверка условия наличия книги в бд
14	IF EXISTS (SELECT * FROM [dbo].Books WHERE title = @BOOK_TITLE)
15	BEGIN
16		-- Проверка условия наличия книги в таблице "Выдачи"
17		IF EXISTS (SELECT 1 FROM @borrowing)
18		BEGIN
19			SELECT * FROM @borrowing ORDER BY returndate DESC
20		END
21		ELSE PRINT N'The history of the book with title = '''
22		+ @BOOK_TITLE + ''' is yet empty!';
23	END
24	ELSE PRINT N'The book with title = ''' + @BOOK_TITLE + ''' does not exist!'
25END
26GO