urthrilled
asked on
Help with a case statement in a SQL Stored Procedure
My company uses a custom eForm built in VS 2013 to route requests for approval.
The stored procedure that inserts the desired routing into the database expects that the Supervisor of the requester is an employee and I now have a situation where that is not so.
I would like to put a case statement in the stored procedure to take care of it, but am unsuccessful and would appreciate some input.
I am attaching the original and my attempt.
Stored-Procedures--original-and-prop.pdf
The stored procedure that inserts the desired routing into the database expects that the Supervisor of the requester is an employee and I now have a situation where that is not so.
I would like to put a case statement in the stored procedure to take care of it, but am unsuccessful and would appreciate some input.
I am attaching the original and my attempt.
Stored-Procedures--original-and-prop.pdf
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pawan Kumar Khowal - I was able to successfully alter the Stored Procedure with your suggestion and it didn't insert the email ('asmith@smith.com') in the SupervisorEmail field for the eForm. It looks for the Supervisor Name from the AD list of "Managers" and grabs their email, but since this one is not an Employee it is in AD as a User, but does not have the same profile as our employees.
Thank you, hope you might have another idea?
Doris
Thank you, hope you might have another idea?
Doris
ASKER
Thank you for your help, it's working now.
Doris (urthrilled)
Doris (urthrilled)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is SO FRUSTRATING!!!!!!!!!!!!!!!
The new website configuration I cannot figure out how to give the best contributor credit and close the question. I've marked the proper contributions twice and still the question is open and I cannot find any other action other than deleting the question or requesting attention...
I've read Experts Exchange tutorial and am not getting the prompts they mention....
The new website configuration I cannot figure out how to give the best contributor credit and close the question. I've marked the proper contributions twice and still the question is open and I cannot find any other action other than deleting the question or requesting attention...
I've read Experts Exchange tutorial and am not getting the prompts they mention....
case @supervisorEmail
when @supervisorName = 'Andy Smith' then 'asmith@smithco.com' else
@supervisorEmail end,
When you declare the main @supervisorEmail you dont need to refer "=" on the clause,
so it's just.
case @supervisorEmail
when @supervisorName then 'asmith@smithco.com' else
@supervisorEmail end
case @supervisorEmail (let's say it's = 'test')
when @supervisorName (if it has the string 'test' aswell it will match and enter on this case, if not it will go to else) then 'asmith@smithco.com' else
@supervisorEmail end
You may could just remove the main and change to
case when @supervisorName then 'asmith@smithco.com' else
@supervisorEmail end