:SetVar Procedure "BKRG::SelectBooks"
:r $(DropProcedureScript_AUX)
create procedure [$(Procedure)]
   @NamePart         $(dt_String_AUX)  = null,
   @ISBNPart         $(dt_String_AUX)  = null,
   @AuthorId         uniqueidentifier  = null,
   @CategoryId       uniqueidentifier  = null,
   @GenreId          uniqueidentifier  = null,
   @PublisherId      uniqueidentifier  = null,
   @SeriesId         uniqueidentifier  = null,
   @ReleaseYear_From int               = null,
   @ReleaseYear_To   int               = null,
   @SortingKey       $(dt_String_AUX)  = null,
   @Status           uniqueidentifier  = null out,
   @Message          $(dt_Message_AUX) = null out
AS
/*==================================================================================================
# BookRegistry app. | Author: Sergei Y.Kitáev
----------------------------------------------------------------------------------------------------
#
# BOOK LIST SELECTION:
#
# === Usage: ===
# exec @RetValue = [BKRG::SelectBooks] @Status=@Status out, @Message=@Message out
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# === Result-set: ===
# [ID]            uniqueidentifier  not null,
# [ISBN]          $(dt_NormalName),
# [Name]          $(dt_NormalName),
# [ReleaseYear]   int,
# [Description]   $(dt_String_AUX),
# [AuthorId]      uniqueidentifier  not null,
# [AuthorName]    $(dt_NormalName)  not null,
# [PublisherId]   uniqueidentifier  not null,
# [PublisherName] $(dt_NormalName)  not null,
# [SeriesId]      uniqueidentifier  not null,
# [SeriesName]    $(dt_NormalName)  not null,
# [CategoryId]    uniqueidentifier  not null,
# [CategoryName]  $(dt_NormalName)  not null,
# [GenreId]       uniqueidentifier  not null,
# [GenreName]     $(dt_NormalName)  not null
# ___ Default order: ___
# (ID-significance), [Name], [ISBN]
--------------------------------------------------------------------------------------------------*/
-- commstat_NothingSpecified
:SetVar Status_IncorrectSortingKey "'F6DB1383-8559-45B0-9844-7E762E7AE3F9'"
--:SetVar Status_ "''"
/*=================================================================================================*/
$(BEGIN_AUX)
----------------------------------------------------------------------------------------------------
select @Status=null, @Message=null
----------------------------------------------------------------------------------------------------
set @NamePart = dbo.[BKRG:PrepareStrParam]( @NamePart )
set @ISBNPart = dbo.[BKRG:PrepareStrParam]( @ISBNPart )
set @SortingKey = dbo.[BKRG:PrepareStrParam]( @SortingKey )
----------------------------------------------------------------------------------------------------
if @NamePart is null and @ISBNPart is null and @AuthorId is null and @CategoryId is null and @GenreId is null and @PublisherId is null and @SeriesId is null and
   @ReleaseYear_From is null and @ReleaseYear_To is null
begin
   select @Status=$(commstat_NothingSpecified), @Message=dbo.[AUX:LocalizedText](
      'Nothing is specified for book list selection.',
      N'Ничего не задано для выборки списка книг.' )
   RETURN 1
end
if @SortingKey is not null and @SortingKey not in ('Name','ISBN','ReleaseYear','AuthorName','PublisherName','SeriesName','CategoryName','GenreName')
begin
   select @Status=$(Status_IncorrectSortingKey), @Message=dbo.[AUX:LocalizedText](
      'Incorrect sorting key parameter.',
      N'Некорректный параметр ключа сортировки.' )
   RETURN 1
end
----------------------------------------------------------------------------------------------------
--raiserror ('Qwertyuiopasdfghjklzxcvbnm.',11,1) -- test error (visible for client-debug)

select
   b.[ID],
   b.[ISBN], b.[Name], b.[Description], b.[ReleaseYear],
   b.[AuthorId]   , a.[Name] 'AuthorName',
   b.[PublisherId], p.[Name] 'PublisherName',
   b.[SeriesId]   , s.[Name] 'SeriesName',
   b.[CategoryId] , c.[Name] 'CategoryName',
   b.[GenreId]    , g.[Name] 'GenreName'
from [Books] b (nolock)
join [Authors] a (nolock)
     on a.[ID] = b.[AuthorId]
join [Publishers] p (nolock)
     on p.[ID] = b.[PublisherId]
join [Series] s (nolock)
     on s.[ID] = b.[SeriesId]
join [Categories] c (nolock)
     on c.[ID] = b.[CategoryId]
join [Genres] g (nolock)
     on g.[ID] = b.[GenreId]
where
   ( @NamePart          is null or CHARINDEX( @NamePart, b.[Name] ) > 0 ) and
   ( @ISBNPart          is null or CHARINDEX( @ISBNPart, b.[ISBN] ) > 0 ) and
   ( @AuthorId          is null or a.[ID] = @AuthorId    ) and
   ( @CategoryId        is null or c.[ID] = @CategoryId  ) and
   ( @GenreId           is null or g.[ID] = @GenreId     ) and
   ( @PublisherId       is null or p.[ID] = @PublisherId ) and
   ( @SeriesId          is null or s.[ID] = @SeriesId    ) and
   ( @ReleaseYear_From  is null or b.[ReleaseYear] >= @ReleaseYear_From ) and
   ( @ReleaseYear_To    is null or b.[ReleaseYear] <= @ReleaseYear_To )
order by
-- SORTING WITH “HEAVY NULLS/INDEFINITES”:
   case @SortingKey when 'ISBN'           then IIF( b.[ISBN] is null, 1, 0 ) else null end,
   case @SortingKey when 'ISBN'           then b.[ISBN] else null end,
   case @SortingKey when 'ReleaseYear'    then IIF( b.[ReleaseYear] is null, 1, 0 ) else null end,
   case @SortingKey when 'ReleaseYear'    then b.[ReleaseYear] else null end,
   case @SortingKey when 'AuthorName'     then a.[Indef] else null end,
   case @SortingKey when 'AuthorName'     then a.[Name] else null end,
   case @SortingKey when 'PublisherName'  then p.[Indef] else null end,
   case @SortingKey when 'PublisherName'  then p.[Name] else null end,
   case @SortingKey when 'SeriesName'     then s.[Indef] else null end,
   case @SortingKey when 'SeriesName'     then s.[Name] else null end,
   case @SortingKey when 'CategoryName'   then c.[Indef] else null end,
   case @SortingKey when 'CategoryName'   then c.[Name] else null end,
   case @SortingKey when 'GenreName'      then g.[Indef] else null end,
   case @SortingKey when 'GenreName'      then g.[Name] else null end,
   IIF( b.[Name] is null, 1, 0 ), b.[Name],
   IIF( b.[ISBN] is null, 1, 0 ), b.[ISBN]

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--waitfor delay '00:00:03' -- test delay (3 sec.)
----------------------------------------------------------------------------------------------------
$(END_AUX)
/*================================================================================================*/
GO
/*
declare @RetValue int, @Status uniqueidentifier, @Message nvarchar(max)
exec @RetValue = [BKRG::SelectBooks] @NamePart='И', @Status=@Status out, @Message=@Message out
if @RetValue<>0 select @RetValue 'RetValue', @Status 'Status', @Message 'Message'
*/
/*
declare @RetValue int, @Status uniqueidentifier, @Message nvarchar(max)
exec @RetValue = [BKRG::SelectBooks] @ISBNPart='3', @Status=@Status out, @Message=@Message out
if @RetValue<>0 select @RetValue 'RetValue', @Status 'Status', @Message 'Message'
*/
/*
declare @RetValue int, @Status uniqueidentifier, @Message nvarchar(max)
exec @RetValue = [BKRG::SelectBooks] @ISBNPart='3', @SortingKey='ISBN', @Status=@Status out, @Message=@Message out
if @RetValue<>0 select @RetValue 'RetValue', @Status 'Status', @Message 'Message'
*/
/*
declare @RetValue int, @Status uniqueidentifier, @Message nvarchar(max)
exec @RetValue = [BKRG::SelectBooks] @NamePart='из', @SortingKey='ISBN', @Status=@Status out, @Message=@Message out
if @RetValue<>0 select @RetValue 'RetValue', @Status 'Status', @Message 'Message'
*/
/*
declare @RetValue int, @Status uniqueidentifier, @Message nvarchar(max)
exec @RetValue = [BKRG::SelectBooks] @Status=@Status out, @Message=@Message out
if @RetValue<>0 select @RetValue 'RetValue', @Status 'Status', @Message 'Message'
*/
