mardi 23 février 2021

How to check multiple conditions in stored procedures for my query using if conditions ,what mistakes i did in my proceudres?

Here My conditions are ,

  1. if workorderi id = 13 -i.e valid number and allworkorderid=' ' then print only one workorderid
  2. if workorderid=1666666-invalidnumber and allworkorderid=' ' then print invalidnumber
  3. if workorderid=845u485u45u4 -invalidnumber and allworkorderid='yes' then print allworkorderid
  4. if workorderid = 13 -validnumber and allworkorderid='yes' then print allworkorderid .
  5. if workorderid=845u485u45u4 -invalidnumber and allworkorderid='no' then print invalid character

Every conditions is working except 5 th one , It displaying both the messages but i want precedence only on string for these condition

i.e

133333,'no' should be a invalid character, what ever the number it may be if it is no it should display invalid character .

Below i gave my code Samples:

create or ALTER PROC Sp_workorders (@workorderid INT,
                          @allworkerid VARCHAR(20))
AS
  BEGIN
  --this is used to check the id , if it exists below condition will execute or else ,it will print as invalid number
      IF EXISTS(SELECT wo.workorderid,
                       wr.productid
                FROM   adventureworks2019.production.workorder wo
                       INNER JOIN adventureworks2019.production.workorderrouting
                                  wr
                               ON wo.workorderid = wr.workorderid
                WHERE  wo.workorderid = @workorderid
                GROUP  BY wo. workorderid,
                          wr.productid)
        BEGIN
        --this will run if exists is true 
            IF ( @workorderid = @workorderid
                 AND @allworkerid = ' ' )
              SELECT Workorders.workorderid,
                     productid,
                     countofoperationsequence,
                     countoflocationid,
                     max_operationseq,
                     max_locationid,
                     completionduration,
                     latedays,
                     category
              FROM   (SELECT wo.workorderid,
                             wo.productid,
                             Count(operationsequence)CountofOperationSequence,
                             Count(locationid)       CountofLocationID,
                             Max(operationsequence)  MAx_operationSeq,
                             Max(locationid)         Max_LocationID
                      FROM   adventureworks2019.production.workorder WO
                             INNER JOIN
                             adventureworks2019.production.workorderrouting
                             WR
                                     ON WO.workorderid = WR.workorderid
                      GROUP  BY wo.workorderid,
                                wo.productid)Workorders
                     INNER JOIN (SELECT workorderid,
                                        Datediff(day, startdate, enddate)
                                        CompletionDuration ,
                                        Datediff(day, duedate,
                                        enddate)   LateDays,
                                        CASE
                                          WHEN enddate > duedate THEN
                                          'Delayed Completion of Work Order'
                                          WHEN enddate = duedate THEN
                                          'On time Completion of Work Order'
                                          WHEN enddate < duedate THEN
                                          'Advance Completion of Work Order'
                                        END                               AS
                                        Category
                                 FROM   adventureworks2019.production.workorder)
                                DaysforCompletingwork
                             ON Workorders.workorderid =
                                DaysforCompletingwork.workorderid
              WHERE  Workorders.workorderid = @workorderid
       end     
            ELSE--Else it will execute this 
              BEGIN
                  PRINT 'invalid number'
              END
     
--it is used to check only one parameter which is string ,whatever the first parameter it ignores it ,it will check only yes.
      IF ( @allworkerid = 'yes ' or @allworkerid=' ' )
      --calling a second procedure which is saved as a seperate file
        EXEC Sp_workorders2
          @workorderid=@workorderid,
          @allworkerid=@allworkerid
      ELSE
        PRINT 'invalid character'
    END 
--Scenarios:
 --exec Sp_workorders 13,'' -- working fine 
 -- exec Sp_workorders 13,'yes'--working fine
 --  exec Sp_workorders 13,'no'--working fine
 --   exec Sp_workorders 137777,'yes '--working fine whatever workorderidnumber it should give only precedence to only yes
    -- exec Sp_workorders 137788,'no' -- not working ,showing both the messages here i want to print message as  invalid  character 

Aucun commentaire:

Enregistrer un commentaire