2nd Column values keep getting repeated

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

2nd Column values keep getting repeated

Author

Topic

asyed01
Starting Member

13 Posts

Posted - 2009-11-03 : 11:09:25

Hello experts,

I’m trying to write a code though which I need to add some data in one of my table. At the moment I’ve defined logic and code works fine and insert data in table. However what I’ve observed is when I try to set more then one variable value (after making decision making with if conditions I’m setting values for @project_no and @job variables) the only first variable value gets set and other’s the only last value gets populated in all the rows. For instance if I’m setting @project_no value first in code then it works absolutely fine but @job value doesn’t get fill in rows accordingly and only last value gets fill in all the columns. To understand it better here is the output

1 ME90 Manager 2009-11-03 10:05:17.167
2 ME91 Manager 2009-11-03 10:05:17.167
3 ME92 Manager 2009-11-03 10:05:17.167
4 ME93 Manager 2009-11-03 10:05:17.167
5 ME94 Manager 2009-11-03 10:05:17.167
1 ME90 Manager 2009-11-03 10:05:17.167
2 ME91 Manager 2009-11-03 10:05:17.167
3 ME92 Manager 2009-11-03 10:05:17.167
4 ME93 Manager 2009-11-03 10:05:17.167
5 ME94 Manager 2009-11-03 10:05:17.167

And to reference here is code.

declare @i integer, @j integer
declare @project_no char(4)
declare @job char(15)
declare @enter_date datetime
set @i = 1
set @j = 1
set @job = 'Analyst'
set @enter_date = getdate()
while @i <= 2
begin
while @j <= 5
begin
if (@j = 1)
set @project_no = 'ME90'
set @job = 'Analyst'
if (@j = 2)
set @project_no = 'ME91'
set @job = 'Junior Cons'
if (@j = 3)
set @project_no = 'ME92'
set @job = 'Technical Cons'
if (@j = 4)
set @project_no = 'ME93'
set @job = 'Sr.Tech Cons'
if (@j = 5)
set @project_no = 'ME94'
set @job = 'Manager'

insert into works_on
values(@j, @project_no, @job, @enter_date )
set @j = @j+1
end
set @i = @i+1
set @j = 1
end
GO

Does anybody know what can be the reason for it? What is my mistake?

Thanks a lot in advance

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-11-03 : 11:27:21

Its because you are setting the date to getdate() outside the loop.

Either move set @enter_date = getdate() inside the while or just put getdate() in you insert:

insert into works_on
values(@j, @project_no, @job, getdate())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources