Condition (if / else) in SQL?

Hi all,

I'm building a DataSet on Visual Studio and don't know how to do a condition (if/else) with SQL...

I have a search form, with a DropDownList and have 2 options in it: Search by Title or Search by Author. If the "Title" is selected, then the value is "title and if "Author" is selected, then the value is "author".

Here is what I have right now for the DataSet, as seperated queries but I think I can combine them to be one single query

1.This will returns the songs that matches the title:

SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK,
LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY
FROM t_lyrics

WHERE ((@.LYRICS_TITLE IS NULL) OR (LYRICS_TITLE LIKE '%' + @.LYRICS_TITLE + '%') OR (LYRICS_TITLE2 LIKE '%' + @.LYRICS_TITLE + '%')) AND (@.LYRICS_TYPE = 'title')

2. This returns the songs that matches the author:

SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER,LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK,
LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY
FROM t_lyrics

WHERE ((@.LYRICS_AUTHOR IS NULL) OR (LYRICS_AUTHOR LIKE '%' +@.LYRICS_AUTHOR + '%') OR (LYRICS_AUTHOR2 LIKE '%' + @.LYRICS_AUTHOR + '%'))AND (@.LYRICS_TYPE = 'author')

This is very inefficient because I have 2 queries, and I need to build 2 ObjectDataSources as well as 2 different GridViews to display the results. I think we can do something like

SELECT ... ...

FROM t_lyrics

if (@.LYRICS_TYPE = 'title')

DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH TITLE

else if (@.LYRICS_TYPE = 'author')

DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH AUTHOR

But I don't know how to write that in T-SQL.

Any help would be greatly appreciated,

Thank you very much,

Kenny.

Example of a simple sp with if else, you can tune your queries in that line i guess:

ifexists (select *fromsysobjectswhere id =object_id ('sp_test'))drop proceduresp_testgocreate proceduresp_test@.typevarchar(1)asif (@.type ='A')beginprint'A was the entered type'returnend elsebeginprint'something else was the type'returnend

|||

Does anyone know how to write it in the query and not as SP?

Thank you very much,

Kenny.

|||

You can use SQL case statements for conditional clauses, too...
http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Does anyone know how to write it in the query and not as SP?

if (@.type ='A')beginprint'A was the entered type'returnend elsebeginprint'something else was the type'returnend