sql proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql proc

Author

Topic

sql_lover
Starting Member

14 Posts

Posted - 2007-03-18 : 10:28:55

i wuld like to write a t-sql procedure (in sql 2000) which performs following operation

i need a procedure that matches current date with the effective date stored in the employee table and if match found then for each employee, updates basic salary of an employee to salary after increment.
pls help me regarding coz i m new to sql and sql procedures
thanx in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-18 : 10:52:06

Sample data!
Expected output!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-18 : 12:49:30

You have to be more specific.

Give us sample data. Give us your expected output based on the sample data.

Peter Larsson
Helsingborg, Sweden

sql_lover
Starting Member

14 Posts

Posted - 2007-03-18 : 13:20:49

ok let me explain in more deep
i hv two tables
1)that stores employee increment details(with amt specified as 'salary after increment') with effective date
2)that stores employye info alng with his current salary

now i want to schedule a job which runs on 1st of every month using a t-sql script in sql 2000

i want this proc (script) to do the following
->it matches the current date with effective date (specified in table 1)
and if date matches then update the field of salary of an employee to new salary(salary after increment)

now my question is that there may be more than one employee those salaries need to be updated

so i suppose this will throw some light to my problem and you'll be able to help me
thanx in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-18 : 13:38:57

Give us table layouts.
Give us proper sample data for the tables.

Give us some idea what the table data should work after some magic intervention.

Peter Larsson
Helsingborg, Sweden

sql_lover
Starting Member

14 Posts

Posted - 2007-03-18 : 13:59:03

Salary increment table

Emp_id 1 2
salary before increment 5200 8000
increment 500 1500
salary after increment 5700 9500
Effective date 1-March-2007 1-Apr-2007

Employee Table

Emp_Id {personal details of employee} Basic salary

1 5200
2 8000
(Matches with salary before increment)

now what I want is on 1st of every month it checks for each and every employee record and updates basic salary to salary after increment if effective date is current date

So on 1st March ,emp table should look like
Emp_Id {personal details of employee} Basic salary

1 5700
2 8000

So on 1stapril, emp table should look like
Emp_Id {personal details of employee} Basic salary

1 5700
2 9500

sql_lover
Starting Member

14 Posts

Posted - 2007-03-19 : 03:35:43

this is what i hv done

--Creating cursor for employee whose salary need to be updated

DECLARE empcode CURSOR FOR
select empcode from trainee.incrementmaster where effectivedate=convert(varchar, getdate(), 101)

--Procedure to update salary
alter procedure sal_temp
as
declare @empcode varchar(20)
declare @sqlStatement varchar(20)
OPEN empcode
FETCH NEXT FROM empcode INTO @empcode

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlStatement = 'update trainee.employee set basic=(select salaryafterincr from trainee.incrementmaster where empcode='+ @empcode + 'where empid=' + @empcode +' ';
EXEC(@sqlStatement)
close empcode
deallocate empcode
end;

this procedures complies with no error
but give runtime errors as follows

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'emplo'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'emplo'.
Server: Msg 16916, Level 16, State 1, Procedure sal_temp, Line 15
A cursor with the name 'empcode' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure sal_temp, Line 16
A cursor with the name 'empcode' does not exist.

pls help me regarding this
thanx in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 03:41:33

Change
declare @sqlStatement varchar(20)

to
declare @sqlStatement varchar(2000)

But I am very sure of there are more clever ways to go.

Peter Larsson
Helsingborg, Sweden

sql_lover
Starting Member

14 Posts

Posted - 2007-03-19 : 09:17:34

i found a even better solution
there is no need to take 'sqlstatemet' variable

queries can be directly executed using following code

CREATE PROC sal_increment
AS
BEGIN
DECLARE @empcode varchar(20)
DECLARE @sqlStatement varchar(20)
OPEN empcode
FETCH NEXT FROM empcode INTO @empcode

WHILE @@FETCH_STATUS = 0
BEGIN
update trainee.employee set basic=(select salaryafterincr from trainee.incrementmaster where empcode=@empcode)
where emp_id=@empcode
END;
CLOSE empcode
END;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 09:23:13

How fast does it run

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 09:27:19

Even better...

update		e
set		e.basic = m.salaryafterincr
from		trainee.employee AS e
inner join	trainee.incrementmaster AS m on m.empcode = e.emp_id

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources