Retrieve all column if parameter is null

angelagke

How I write the condition of SQL statement to retrieve all author's name from database pubs if passing parameter is null and if user insert an author's name, then only particular name will be display.. thanks angela

nguyenvhn

CREATE PROCEDURE GetAuthors @AuthorName nvarchar(100) AS IF @AuthorName IS NULL SELECT AuthorName FROM Author ELSE SELECT AuthorName FROM Author WHERE AuthorName=@AuthorName Is that one?

angelagke

when i run your SQL statement, it not return any value if i didn't insert author's name. I get another SQL statement that can solve my problem, Declare @au_lname char(30) Select @au_lname = '' select title, price, Name = substring (au_lname, 1,40), type from titleauthor ta JOIN authors a on ta.au_id = a.au_id JOIN titles t on ta.title_id = t.title_id Where 1 = Case When RTRIM(@au_lname) = '' Then 1 When RTRIM(@au_lname) <> '' And RTRIM(UPPER(@au_lname)) = RTRIM(UPPER(au_lname)) Then 1 Else 0 End it's cool;) angela

r stropek

Hi! You can simplify this statement:

Declare @au_lname char(30)
Select @au_lname = ''

select title, price, Name = substring (au_lname, 1,40), type
from titleauthor ta
JOIN authors a on ta.au_id = a.au_id
JOIN titles t on ta.title_id = t.title_id
where RTRIM(@au_lname) = ''
or RTRIM(UPPER(@au_lname)) = RTRIM(UPPER(au_lname))

Regards, Rainer. Rainer Stropek Visit my blog at http://www.cubido.at/rainers

angelagke

ok, thanks for your solution angela