Help in Stored Procedure Required

[Former Member]

Dear All,

I have an udf in my Sales Order u_EnqNo which has some numbers. I want to have a check if while booking the Sales Order if some particular numbers are there then in the project code field which is in the row level of Sales Order should have project code ending with SP ( Special Project).

I am trying to make an Stored Procedure but its somehow not working and been asking for SP when already SP is there.

--Project code for Sales Order - Special Projects
if  @transaction_type IN (N'A', N'U') AND (@Object_type IN ('17'))
begin
if exists (select * from rdr1 b,ordr a 
where b.DocEntry=@list_of_cols_val_tab_del 
and (b.Project is null or b.Project='' or b.Project NOT Like '%%_SP' and a.DocEntry=b.Docentry 
and a.u_EnqNo ='95021729' or a.u_EnqNo ='95021970' or a.u_EnqNo ='95022171' or
a.u_EnqNo ='95021972' or a.u_EnqNo ='95022210' or a.u_EnqNo ='95017240' or
a.u_EnqNo ='95010501' or a.u_EnqNo ='95021280' or a.u_EnqNo ='95020277' or
a.u_EnqNo ='95021957' or a.u_EnqNo ='95017862' or a.u_EnqNo ='95021093' or
a.u_EnqNo ='95020915' or a.u_EnqNo ='95021907' or a.u_EnqNo ='95015477' or
a.u_EnqNo ='95100300' or a.u_EnqNo ='95100354' or a.u_EnqNo ='95100349' or
a.u_EnqNo ='95100350' or a.u_EnqNo ='95028879' or a.u_EnqNo ='95021454' or
a.u_EnqNo ='95100350' or a.u_EnqNo ='95028879' or a.u_EnqNo ='95021454' or
a.u_EnqNo ='95021666' or a.u_EnqNo ='95021519' or a.u_EnqNo ='95022148'))
begin
select  @error=10 , @error_message = N' Special Project...Provide Special Project Code with Suffix as SP'
end
end

Kindly advise.

Regards,

Swamy

[former_member204969 · Accepted solution]

You did not consider the precedence of the logical operators!

Try this:

--Project code for Sales Order - Special Projects
if  @transaction_type IN (N'A', N'U') AND (@Object_type IN ('17'))
begin
if exists (select * from rdr1 b,ordr a 
where b.DocEntry=@list_of_cols_val_tab_del 
and (b.Project is null or b.Project='' or b.Project NOT Like '%%_SP' and a.DocEntry=b.Docentry 
and 
(a.u_EnqNo ='95021729' or a.u_EnqNo ='95021970' or a.u_EnqNo ='95022171' or
a.u_EnqNo ='95021972' or a.u_EnqNo ='95022210' or a.u_EnqNo ='95017240' or
a.u_EnqNo ='95010501' or a.u_EnqNo ='95021280' or a.u_EnqNo ='95020277' or
a.u_EnqNo ='95021957' or a.u_EnqNo ='95017862' or a.u_EnqNo ='95021093' or
a.u_EnqNo ='95020915' or a.u_EnqNo ='95021907' or a.u_EnqNo ='95015477' or
a.u_EnqNo ='95100300' or a.u_EnqNo ='95100354' or a.u_EnqNo ='95100349' or
a.u_EnqNo ='95100350' or a.u_EnqNo ='95028879' or a.u_EnqNo ='95021454' or
a.u_EnqNo ='95100350' or a.u_EnqNo ='95028879' or a.u_EnqNo ='95021454' or
a.u_EnqNo ='95021666' or a.u_EnqNo ='95021519' or a.u_EnqNo ='95022148'
)))
begin
select  @error=10 , @error_message = N' Special Project...Provide Special Project Code with Suffix as SP'
end
end

Or you can use a more simple form using this:

a.u_EnqNo in ('95021729', . . ., '95022148')

[Former Member]

Dear Swamy,

Try this one:

if @transaction_type IN (N'A', N'U') AND (@Object_type IN ('17'))

begin

if exists (select * from rdr1 b inner join ordr a ON a.DocEntry=b.Docentry

where a.DocEntry=@list_of_cols_val_tab_del

and (b.Project is null or b.Project='' or b.Project NOT Like '%SP')

and a.u_EnqNo in ('95021729','95021972','95010501','95021957','95020915','95100300','95100350','95100350','

95021666','95021970','95022210','95021280','95017862','95021907','95100354','95028879','95028879',

'95021519','95022171','95017240','95020277','95021093','95015477','95100349','95021454','95021454','95022148')

begin

select @error=10 , @error_message = N'Provide Special Project Code with Suffix as SP'

end

end

Thanks,

Gordon