3

A similar question has been asked here

Anyway i get a syntax error i cannot figure.

This is my code:

declare @MyParameter integer
se @MyParameter = Set At Runtime (could be -1 or any value >=1)

SELECT manyfields FROM manyjoinedtables
where
 case when @MyParameter> -1 then 
 (FIELD1 **=** @MyParameter AND ANOTHERFIELD = Value**)**
 end -- note: in case @MyParameter  = -1 i do not want to add where condition

Anyway Management studio underlines in red the 2 chars surrounded by ** above.

Why? Where is the syntax error?

Community
  • 1
  • 1
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249

1 Answers1

2

Give this a go;

DECLARE @MyParameter INT
SET @MyParameter = Set At Runtime (could be -1 or any value >=1)

SELECT manyfields 
FROM manyjoinedtables
WHERE
    @MyParameter <= -1
OR
(
    @MyParameter > -1
    AND FIELD1 = MyParameter 
    AND AnotherField = Value
)
Rich Benner
  • 7,873
  • 9
  • 33
  • 39
  • Thanks, it works, so the syntax in the accepted answer in the linked post is wrong. – UnDiUdin Jun 14 '16 at 15:11
  • Not sure, it may work. It's better for performance if you're not carrying out calculations in your where clauses though, so I'd definitely go for this logic. If this answers your question then please feel free to mark the answer as accepted. – Rich Benner Jun 14 '16 at 15:12
  • 3
    @user193655 that syntax will work in some RDBMSs if they have a Boolean datatype as each branch of the case is a Boolean and the dataype of the case expression as a whole will be boolean. But SQL Server doesn't. – Martin Smith Jun 14 '16 at 15:14
  • 1
    @user193655 I don't know how my answer got upvoted and accepted. it clearly is not working and I'm not sure what I was thinking. This looks good though! – Brad Jun 14 '16 at 15:33
  • 1
    @Brad the OP is on Postgres and it probably works there. So you just need to remove the misleading SQL server statement. – Martin Smith Jun 14 '16 at 15:34
  • 1
    Thanks to everyone. I accepted the answer (there is a 10 minutes wait time before it is possible to accept). – UnDiUdin Jun 14 '16 at 15:36
  • 1
    @MartinSmith yeah it works in Postgres. I edited that answer though to be more clear. – Brad Jun 14 '16 at 16:33