November 3, 2009 at 9:07 am
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
1ME90Manager 2009-11-03 10:05:17.167
2ME91Manager 2009-11-03 10:05:17.167
3ME92Manager 2009-11-03 10:05:17.167
4ME93Manager 2009-11-03 10:05:17.167
5ME94Manager 2009-11-03 10:05:17.167
1ME90Manager 2009-11-03 10:05:17.167
2ME91Manager 2009-11-03 10:05:17.167
3ME92Manager 2009-11-03 10:05:17.167
4ME93Manager 2009-11-03 10:05:17.167
5ME94Manager 2009-11-03 10:05:17.167
And to reference here is code.
declare @i integer, @j-2 integer
declare @project_no char(4)
declare @job char(15)
declare @enter_date datetime
set @i = 1
set @j-2 = 1
set @job = 'Analyst'
set @enter_date = getdate()
while @i <= 2
begin
while @j-2 <= 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 )
end
set @i = @i+1
set @j-2 = 1
end
GO
Does anybody know what can be the reason for it? What is my mistake?
Thanks a lot in advance
November 3, 2009 at 9:19 am
It would also help if you provided the DDL (CREATE TABLE statement(s)) for the table(s), sample data (as INSERT statements). What you have provided so far really isn't enough.
For more help on how to post data and get the best help, please read the first article I have referenced below in my signature block regarding "Asking for help".
November 3, 2009 at 9:20 am
I am not sure what you are trying to do, but the problem may be with your IF structures. When you write:
if (@j = 4)
set @project_no = 'ME93'
set @job = 'Sr.Tech Cons'
only the first statement following IF is executed conditionally. The rest of the statements are executed regardless of the IF outcome. If you need to run more than one statement conditionally than you need to enclose them in a BEGIN...END structure:
if (@j = 4)
begin
set @project_no = 'ME93'
set @job = 'Sr.Tech Cons'
end
November 3, 2009 at 9:53 am
SSCrazy Elights I apologize for not providing the full information.
Grasshopper thanks for you input and yes I understand my mistake.
I'm trying to learn and wasn't sure about what difference begin and end can make.
All I'm trying to do is add data based on different conditions
Thanks for your help.
November 3, 2009 at 10:00 am
mr_adeelalisyed (11/3/2009)
SSCrazy Elights I apologize for not providing the full information.Grasshopper thanks for you input and yes I understand my mistake.
Hey, Those are not their Profile Names, they are just a Names given the users based on their Level in the SQLServerCentral.com Site.
A New user would always be given as grasshopper, and as you go further, it would change, and here it is Lynn Pettis, Al-279884 is their Profile Name...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply