CODEWITHSUNDEEP
t-sql
SheilaW
SheilaW

Reputation: 1

T-SQL CURSOR with blank output

Working on SQL CURSOR and encounter blank output amongst right output. Check on pubs database and got the blank output too. Script for pubs

DECLARE @auid varchar(30)
DECLARE CURSOR_AuthorTitle CURSOR FOR
    SELECT au_id FROM authors order by au_lname
OPEN CURSOR_AuthorTitle
    FETCH NEXT FROM CURSOR_AuthorTitle INTO @auid 
    WHILE @@Fetch_Status = 0 
    BEGIN
        ;With authortitle_id as 
        (SELECT a.au_id, title_id, au_fname, au_lname, royaltyper 
        FROM authors a, titleauthor ta 
        WHERE a.au_id=ta.au_id) 

        SELECT 'Author Name' =  au_fname +' ' + au_lname, 'Book Title'= title 
        FROM authorTitle_id at, titles t 
        WHERE at.title_id=t.title_id and au_id=@auid   
        ORDER BY au_lname

    FETCH NEXT FROM CURSOR_AuthorTitle INTO @auid
    END
CLOSE CURSOR_AuthorTitle
DEALLOCATE CURSOR_AuthorTitle

Got the author & book list, but with some blank outputs as well. I want to get rid of the blank output. Thanks for your help in advance.

Upvotes: 0

Views: 1538

Answers (1)

KM.
KM.

Reputation: 103707

Watch out when you concatenate NULL values. if you have blank columns it is because you have some NULL columns. Remember that:

'your string'+null = null

try using ISNULL() like this:

'your string'+ISNULL(null,'') = 'your string'

your code:

SELECT 'Author Name' =  au_fname +' ' + au_lname, 'Book Title'= title

should be:

SELECT 'Author Name' =  ISNULL(au_fname+' ','') + ISNULL(au_lname,'')

If you have blank rows, it is because you authors that do not have any titles. I not sure why you don't just have a single query for this and forget the cursor.

Upvotes: 2

Related Questions