question

rajendrasedhain avatar image
rajendrasedhain asked

Stored Procedure if else condition

Hello all,

I have three tables: Book, author and Book_Author. The third table make them one to many relation.One book may have more than one authors. They have the columns:

Book: BookID, Title

Author: ID, AuthorNameFirst,AuthorLastName, AuthorName

Book_Author: ID, BOOKID, AuthorID, AuthorPriority

I have a stored procedure which displays title and it's associate authors:

ALTER PROCEDURE [dbo].[Item_Author]

AS

DECLARE @tblBookAuthor table
(rowId int identity(1,1),
BookID nvarchar(10),
Title varchar(50),
Authors varchar(500)
)
Declare @BookID varchar(10),
@Title varchar(200),
@Authors varchar(500),
@Author varchar(100)

DECLARE Book CURSOR FAST_FORWARD FOR
SELECT BookID, Title from Book
Order by Title

OPEN Book
WHILE 1=1
BEGIN
FETCH NEXT FROM Book INTO @BookID, @Title
IF @@FETCH_STATUS <>0
BREAK

    SET @Authors=''                         
    set @Author=''                         
    DECLARE BookAuthor CURSOR FAST_FORWARD FOR                         
    SELECT AuthorName from Author a join BookAuthor ba on a.AuthorID=ba.AuthorId where ba.BookId=@BookID                         
    OPEN  BookAuthor                          
    WHILE 1=1                          
    BEGIN                           
    FETCH NEXT FROM BookAuthor INTO @Author                          
           IF @@FETCH_STATUS <>0                            
                    
           set @Authors=@Authors + ' , ' + @Author                          
    END                         
    CLOSE BookAuthor                          
    DEALLOCATE BookAuthor                          
    insert into @tblBookAuthor(BookID,Title,Authors) values(@BookID,@Title,@Authors)                    

END

CLOSE Book
DEALLOCATE Book

Select * From @tblBookAuthor

return

I want to display the authorname depends on AuthorPriority. If the authorPriority has one, it should display lastname, firsname + other authors + 'and' last author:

Let say, title 'SQL QUery' has three authors in the orderPriority 1,2,3:

John michael, hilly clinton, Jorge buss

then it should display:

Michael, John, Hilly Cilton and Jorge buss.

How can I check if authorPriority =1 set this + remianing other and last authors ??

How can i change my above procedure for this output ??

Thanks

sql-server-2008sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TG avatar image
TG answered

Seems like authorPriority should be in Book_Author in case an author has different priorities for different books they wrote...but anyway...Get rid of the nested cursors. Perhaps this:

select b.bookid            
       ,b.title            
       ,left(a.authors, len(a.authors) - 1) as authors            
from   book b            
cross apply (            
       select case when ba.authorPriority = 1 then a.authorLastName + ', ' + a.AuthorNameFirst else a.authorName end + ', '            
       from   book_author ba            
       inner join author a on a.id = ba.authorid            
       where  ba.bookid = b.bookid            
       order by authorPriority            
       for xml path('')            
       ) a (authors)            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rajendrasedhain avatar image
rajendrasedhain answered

Thanks, TG; your are right AuthorPriority should be in Book_Author table. I moved that.

Can you change the code now ??

Thanks

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.