January 8, 2018 at 4:31 am
Hi following is my stored procedure, its will help me calculate went a particular staff is due for promotion and insert into the table below.
ALTER PROCEDURE [dbo].[uspGetEmployee]
@DOLA nvarchar(50), @Gl nvarchar(50)
AS
DECLARE @years_old integer, @dob_date nvarchar(50), @staffid int, @deptid int, @level nvarchar (50)
BEGIN
-- Step 1: Get the date of last promotion
SELECT @dob_date = D.DOLA,
@level = D.Gl
FROM Staff D;
SELECT @years_old = ( datediff (dd, CONVERT(date, @dob_date), getdate() ) / 365 )
if(@years_old = 4 and @Gl >= 15)
Begin
SELECT @staffid = ed.StaffId, @deptid = ed.DeptID
FROM Staff ed
WHERE ed.DOLA = @DOLA and ed.Gl = @Gl
insert into SeniorProm (QualifyDate,CreatedDate,DeptId,StaffID) values (GETDATE(),GETDATE(), @deptid,@staffid)
end
if(@years_old = 3 and @Gl >= 14)
Begin
SELECT @staffid = ed.StaffId, @deptid = ed.DeptID
FROM Staff ed
WHERE ed.DOLA = @DOLA and ed.Gl = @Gl
insert into SeniorProm (QualifyDate,CreatedDate,DeptId,StaffID) values (GETDATE(),GETDATE(), @deptid,@staffid)
end
if(@years_old = 2 and @Gl >= 6)
Begin
SELECT @staffid = ed.StaffId, @deptid = ed.DeptID
FROM Staff ed
WHERE ed.DOLA = @DOLA and ed.Gl = @Gl
insert into JnrProm (QualifyDate,CreatedDate,DeptId,StaffID) values (GETDATE(),GETDATE(), @deptid,@staffid)
end
End
January 8, 2018 at 4:52 am
This was removed by the editor as SPAM
January 8, 2018 at 5:14 am
What is your question?
Also, you're declaring your parameters @DOLA and @GI as nvarchars, but then comparing them to integers. Why? If someone passes a non-numeric value to these, then your SP is going to fail.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 8, 2018 at 5:37 am
The two different promotion tables are a concern in terms of DB design. You can also combine two of those IF statements into one, as what they do is identical.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2018 at 6:11 am
Thom A - Monday, January 8, 2018 5:14 AMWhat is your question?Also, you're declaring your parameters @DOLA and @GI as nvarchars, but then comparing them to integers. Why? If someone passes a non-numeric value to these, then your SP is going to fail.
Bro, i use the integer for number of year. example SELECT @years_old = ( datediff (dd, CONVERT(date, @dob_date), getdate() ) / 365 ). bro the the SP is not working ... i'm stock here please
January 8, 2018 at 6:14 am
Thom A - Monday, January 8, 2018 5:59 AMAlso, just noticed that @dob_date is an nvarchar as well. If it's a date, it's should be declared as so.
@dob_date its a date bro, what should i do ...need an advice please
January 8, 2018 at 6:16 am
Thom A - Monday, January 8, 2018 5:14 AMWhat is your question?Also, you're declaring your parameters @DOLA and @GI as nvarchars, but then comparing them to integers. Why? If someone passes a non-numeric value to these, then your SP is going to fail.
yes bro,@DOLA and @GL are both nvarchar... with you quote above , please kindly advise.
January 8, 2018 at 6:38 am
Please don't call me "bro", with respect, we aren't that familiar.
As for your data types, as I said, declare them as what they are; you said yourself that @dob_date is a date, so declare it as one. Your other 2 parameters appear to be integers, so the same idea applies here.
What do you mean by "not working"?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 8, 2018 at 6:56 am
Thom A - Monday, January 8, 2018 6:38 AMPlease don't call me "bro", with respect, we aren't that familiar.As for your data types, as I said, declare them as what they are; you said yourself that @dob_date is a date, so declare it as one. Your other 2 parameters appear to be integers, so the same idea applies here.
What do you mean by "not working"?
sorry for calling you bro, my apology...
i have change it still the same
January 8, 2018 at 6:57 am
mrplayerplanet - Monday, January 8, 2018 6:56 AMsorry for calling you bro, my apology...
i have change it still the same
What is still the same? Can you please elaborate? What is your question, and what do you mean by "not working"?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 8, 2018 at 10:21 am
Bluntly speaking, your code requires lots of improvement.
1. Your Step 1 will totally screw up the result because you are assigning DOLA and GI to two variables. The query does not even have a WHERE clause. I'm sure you have more than one entry in the Staff table.
2. As GilaMonster said, using two promotion tables is not appropriate.
3. if(@years_old = 4 and @Gl >= 15) and if(@years_old = 3 and @Gl >= 14) can be replaced with one condition if(@years_old >= 3 and @Gl >= 14)
4.
SELECT @staffid = ed.StaffId, @deptid = ed.DeptID
FROM Staff ed
WHERE ed.DOLA = @DOLA and ed.Gl = @Gl
insert into SeniorProm (QualifyDate,CreatedDate,DeptId,StaffID) values (GETDATE(),GETDATE(), @deptid,@staffid)
can be greatly simplified into a
insert into SeniorProm (QualifyDate,CreatedDate,DeptId,StaffID) SELECT ...
Please try to understand (google) what everyone is trying to tell you. Until this moment no one knows what you are complaining about. No one will try to replicate your tables and codes to help you solve the issue. So please provide as much details as possible. For column names like DOLA and GI, at least give us some description.
January 8, 2018 at 4:12 pm
RandomStream - Monday, January 8, 2018 10:21 AM
3.if(@years_old = 4 and @Gl >= 15)andif(@years_old = 3 and @Gl >= 14)can be replaced with one conditionif(@years_old >= 3 and @Gl >= 14)
These are not logically equivalent. The OP's first condition will only return records when @years_old = 4 if @GL is 15 or greater. Your combined version will return @years_old = 4 when @GL = 14.
January 8, 2018 at 4:26 pm
Please provide details on what is meant by "not working." If you had car trouble would you go to a mechanic and just say "it's broken?"
January 8, 2018 at 4:52 pm
doug.brown - Monday, January 8, 2018 4:12 PMRandomStream - Monday, January 8, 2018 10:21 AM
3.if(@years_old = 4 and @Gl >= 15)andif(@years_old = 3 and @Gl >= 14)can be replaced with one conditionif(@years_old >= 3 and @Gl >= 14)These are not logically equivalent. The OP's first condition will only return records when @years_old = 4 if @GL is 15 or greater. Your combined version will return @years_old = 4 when @GL = 14.
You're correct, Doug. My bad.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply