Value is not inserted into table

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

General SQL Server Forums

New to SQL Server Programming

Value is not inserted into table

Author

Topic

vinoth
Starting Member

14 Posts

Posted - 2008-10-04 : 07:51:06

alter proc [dbo].[answer]
@date datetime

As
BEGIN
Declare @Staff_id int
Declare @a int
Declare @b int
Declare @c int
Declare @ml int
Declare @rml int
Declare @r1 int
Declare @r2 int
Declare @r3 int
Declare @s1 decimal(18,2)
Declare @s2 decimal(18,2)
Declare @s3 decimal(18,2)
Declare @rs decimal(18,2)

--declare @id int

DECLARE my_cursor CURSOR FOR
SELECT * FROM Staff_Master_Table where Dormant_yr = 'y'
OPEN my_cursor

FETCH NEXT FROM my_cursor into @staff_id
WHILE @@FETCH_STATUS = 0
BEGIN

Set @a = (select sum(absent)as noofabsent from staff_attendance_mgt where Staff_id = @Staff_id and
month(date) = month(@date))
Set @b = (select cl from s_leave)
Set @c = @b/12

If(@a>@c)
Set @r1 = @a - @c
Else
Set @r1 = 0

Set @ml = (select sum(absent)as noofabsent from staff_attendance_mgt where staff_id =@staff_id )
Set @rml = (select ml from s_leave)

If(@ml>@rml)
Set @r2 = @ml - @rml
Else
Set @r2 = 0
Set @r3 = @r1 + @r2

Set @s1 = (select ss.netsalary from Staff_Master_Table s join S_Staff_Salary sss on s.id = sss.staff_id
join Staff_Salary_Structure ss on ss.staff_id = sss.salary where s.id = @staff_id )

Set @s2 = @s1 / 30
Set @s3 = @r3 * @s2
Set @rs = @s1 - @s3

If Exists(Select staff_id From Final_Staff_Salary Where staff_id =@staff_id and month(date) = month(@date))
print 'exist'
Else
Insert Into Final_Staff_Salary values(@staff_id,@date,@r3,@s1,@s3,@rs)

FETCH NEXT FROM my_cursor into @staff_id
SELECT 'fetch status is ' + CAST(@@FETCH_STATUS AS VARCHAR)
END
CLOSE my_cursor
DEALLOCATE my_cursor
END

Hello,

this procedure is executed successfully,but i am not getting values inserted into final_salary_table.please provide me solution

vinoth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 08:07:06

will the following return only a single value?

select cl from s_leave
and

select ml from s_leave?
else you cant assign result onto a variable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 08:15:55

Also i belive here also you're learning usage of cursor as i feel your problem can be achieved with a set based solution.

vinoth
Starting Member

14 Posts

Posted - 2008-10-04 : 08:41:04

it should return one table with 6 variables which i have inserted into final_staff_salary.query is executing but values are not inserting into table final_staff_salary

vinoth

vinoth
Starting Member

14 Posts

Posted - 2008-10-04 : 08:51:39

what will be the reason value is not inserting into table.please rectify my error

vinoth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 08:51:45

quote:
Originally posted by vinoth

it should return one table with 6 variables which i have inserted into final_staff_salary.query is executing but values are not inserting into table final_staff_salary

vinoth

i was asking about queries that i posted.will that return single value or resultset?

vinoth
Starting Member

14 Posts

Posted - 2008-10-04 : 09:07:00

yes it will return single
value

vinoth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 09:14:12

and you're sure that values you're trying to insert dont exist already in the table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources