Variable with <> or =

Question

Variable with <> or =

orenk

Old Hand

Points: 388
More actions
October 7, 2015 at 6:03 am

#303926

Hello,
who can I use a variable like the followig
set @MAG3 = '<> 0'
or
set @MAG3 = '= 0'
for the following where condition
where patindex('%;' + Mitarbeiter + ';%' , @MAG2) <> 0
I like to change the <> 0 Parameter but
where patindex('%;' + Mitarbeiter + ';%' , @MAG2) @MAG3
don`t work.
Thank for help.
Olaf

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

orenk (10/7/2015)
Hello,
who can I use a variable like the followig
set @MAG3 = '<> 0'
or
set @MAG3 = '= 0'
for the following where condition
where patindex('%;' + Mitarbeiter + ';%' , @MAG2) <> 0
I like to change the <> 0 Parameter but
where patindex('%;' + Mitarbeiter + ';%' , @MAG2) @MAG3
don`t work.
Thank for help.
Olaf

No, that's not going to work. Can you take a bit of time to explain the logic you are trying to achieve? There will be another way to achieve it, I'm sure.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

orenk Old Hand Points: 388 More actions · Answer 2

Hello,

I have in my select a column called "Mitarbeiter". I like to search for Mitarbeiter in ('SB','MS','BE') or Mitarbeiter not in ('SB','MS','BE')

The 'IN' or 'NOT IN' I want to change via a variable. (For SQL Server Reports)

so I come to the patindex and the change between <>0 and =0

But how to change it?

Olaf

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

orenk (10/7/2015)
Hello,
I have in my select a column called "Mitarbeiter". I like to search for Mitarbeiter in ('SB','MS','BE') or Mitarbeiter not in ('SB','MS','BE')
The 'IN' or 'NOT IN' I want to change via a variable. (For SQL Server Reports)
so I come to the patindex and the change between <>0 and =0
But how to change it?
Olaf

There's a simple way to do this:

WHERE (@Var3 = 'IN' AND Mitarbeiter IN ('SB','MS','BE'))

OR (@Var3 = 'NOT IN' AND Mitarbeiter NOT IN ('SB','MS','BE'))

It's called a "Catch-all" query and has known limitations. Gail Shaw has a good account of those limitations here[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

orenk Old Hand Points: 388 More actions · Answer 4

orenk

Old Hand

Points: 388

October 7, 2015 at 6:58 am

#1831977

That works.

Thanks a lot. :satisfied: