Conditional case for dynamic query

Transact-SQL
, ,
1

I work on SQL server 2012 I face issue on code below when

EStrat value is

number as sample >1000 or <1000 or =1000 as sample it working good

because it will use AcceptedValuesOption_Value and this field is integer so no issue on that

issue happen when EStrat value is <>'Solid State Relay' or ='Solid State Relay'

so it will use field AcceptedValuesOption_Value and this field is integer so it is wrong and it will stuck

correct is when EStrat value is <>'Solid State Relay' or ='Solid State Relay' use Name

so How to correct statement below to do that

SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' ,' And ' +  case when PATINDEX('%[><=]%', EStrat)>0  then 
    'AcceptedValuesOption_Value ' 
    when EStrat is null then
    'Name '
    else 
    'Name '
    end  ) , case when PATINDEX('%[><=]%', EStrat)>0 then
 CAST(EStrat AS NVARCHAR(2500))
 when EStrat is null then
 ' is null '
 else
 ''+CAST(EStrat AS NVARCHAR(2500)) +''
 end ,')')

what i try but not working

SET @ConStr= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' ,' And ' + case when PATINDEX('%[><=]%', EStrat)>0 and isnumeric(EStrat)=0 then

2

No sample data so inpossible to help you.

3 
create table #Condition
(
EStrat varchar(50),
AcceptedValuesOption_Value int,
[Name]  nvarchar(100)
)
insert into #Condition(EStrat,AcceptedValuesOption_Value,[Name])
values
('''=Solid State Relay''',12,'Solid State Relay'),
('''<>Solid State Relay''',12,'Iron'),
('''>1000''',2000,'Iron'),
('''<>1000''',500,'Iron'),
('''=1000''',1000,'Iron')
4

And what is the final output you want displayed?

5

to show what i need to do
this statement below

 case when PATINDEX('%[><=]%', EStrat)>0  then 
    'AcceptedValuesOption_Value ' 
    when EStrat is null then
    'Name '
    else 
    'Name '
    end  )

this above statment is working for >100 and <100 and =100
but if i use <>Solid State Relay or =Solid State Relay
it give me error

Fail :Error converting data type varchar to float

so i need to add new case when

to detect string after <> or =

correct pseudo code

case when PATINDEX('%[><=]%', EStrat)>0 and EStrat have charachters as abcd  then 
Name represent text after charachter <> or =
else 
AcceptedValuesOption_Value  --represnt number
6

in past posts I have encouraged you to take some in depth SQL related courses? Do you have any update on that? This is for your own good. As we don't want to do the work that you are hired to do. This is a simple problem you should be able to resolve on your own but you are overly dependent on others to do it for you.

2 Likes