CODEWITHSUNDEEP
sql
dilipkumar1007
dilipkumar1007

Reputation: 363

how to use if else in sql server with where clause

CREATE PROCEDURE findlist @comedianname1 nvarchar(30),@comedianname2 nvarchar(30)
AS
SELECT comedian 
FROM Comedian
if @comedianname2!=null
begin
WHERE comedian! = @comedianname1 and comedian! = @comedianname2
end
else
begin
WHERE comedian! = @comedianname1
end
GO

I am getting this error : Msg 156, Level 15, State 1, Procedure findlist, Line 7 Incorrect syntax near the keyword 'WHERE'.

Upvotes: 1

Views: 57

Answers (3)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

CREATE PROCEDURE findlist @comedianname1 nvarchar(30),@comedianname2 nvarchar(30)
AS
  SELECT comedian 
  FROM Comedian
  WHERE (comedian! = @comedianname1 and comedian! = @comedianname2) OR (comedian! = @comedianname1)
GO

Upvotes: 1

Naveen
Naveen

Reputation: 1502

You need to use the below query

SELECT comedian 
FROM Comedian
WHERE comedian! = @comedianname1 and (comedian! = @comedianname2 OR @comedianname2 IS NULL)

Upvotes: 2

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You can not use IF in select. You could do this instead in your case:

SELECT comedian 
FROM Comedian
WHERE
    comedian! = @comedianname1 AND
    comedian! = ISNULL(@comedianname2, comedian!)

What does it do? If compares comedian! to @comedianname1 and if @comedianname2 is not null, it compares comedian! to @comedianname2. Otherwise it compares comedian! to comedian!, which is always true.

Upvotes: 3

Related Questions