CODEWITHSUNDEEP
sqlsql-servert-sql
Ramesh Sivaraman
Ramesh Sivaraman

Reputation: 1305

Nested if else statement

I am creating a function to return '0' or '1' depending on the result of nested if else statements. Using MSSQL.

ALTER FUNCTION udf_check_names_starting_with_quotationmark
(@string NVARCHAR(100))
RETURNS INT
AS
BEGIN
  DECLARE @condition INT
  SET @string = LTRIM(@string)
  SET @string = RTRIM(@string)

  IF (PATINDEX('"%', @string) !=0)
    BEGIN
      SET @condition = 1
    END
  ELSE IF (PATINDEX('%"%', @string) !=0) 
    BEGIN
      SET @condition=1
    END
  ELSE IF (PATINDEX('%"', @string) !=0)
    BEGIN
      SET @condition = 1
    END
  ELSE
    BEGIN
      SET @condition=0
    END
  RETURN @condition
END

Everything is working fine with this. Is there a better way to achieve this (I have tried using OR but SQL editor showing an error, not recognizing the OR).

Upvotes: 7

Views: 22577

Answers (2)

user359040
user359040

Reputation:

Surely this can be simplified to:

  IF (PATINDEX('%"%', @string) !=0) 
    BEGIN
      SET @condition=1
    END
  ELSE
    BEGIN
      SET @condition=0
    END

- since PATINDEX('%"%', @string) will be > 0 where either PATINDEX('"%', @string) or PATINDEX('%"', @string) are > 0?

Upvotes: 3

muhmud
muhmud

Reputation: 4604

SET @condition = (case when PATINDEX('"%', @string) !=0 or 
                            PATINDEX('%"%', @string) !=0 or 
                            PATINDEX('%"', @string) !=0 then 1 else 0 end)

Upvotes: 6

Related Questions