help with the while loop in procedure

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

help with the while loop in procedure

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-10-14 : 09:43:30

when i run this below in my procedure i was getting the error near while loop as
Msg 102, Level 15, State 1, Procedure dbo.msp_getEventIDUserIDCompletedDateQueueEnteredDate, Line 64
Incorrect syntax near '='.

insert into @temp2(pkgid)
select pkgid from AUDIT_MetricsInterp
where AuditStampCompleted is null

declare @x int
set @x = (select count(*) from @temp2)

WHILE(x>0)
BEGIN
set @AuditStampCompleted = Null
set @userID = Null
exec [msp_UseridAuditStampCompleted]@pkgid,@AuditStampCompleted = @AuditStampCompleted output,@userID = @userid output
exec [msp_EventIDAuditStampQueueEntered]@pkgid,@AuditstampQueueEntered = @AuditstampQueueEntered output,@QueueEventID = @QueueEventID output
set @AuditStampLocked = (SELECT [dbo].[mfun_AuditStampLocked] (@pkgid))

UPDATE AUDIT_MetricsInterp
SET USERID = @userid,QueueEventID = @QueueEventID,AuditStampLocked=@AuditStampLocked,
AuditStampCompleted = @AuditStampCompleted,AuditstampQueueEntered=@AuditstampQueueEntered
WHERE PKGID = @pkgid

set x= x-1

END

Thanks,
reacha

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 09:57:12

Your varible x is named @x and not only x.

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-14 : 10:08:58

I hate when that happens

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 10:14:22

quote:
Originally posted by X002548

I hate when that happens

Brett

8-)

You are not alone

No, you're never too old to Yak'n'Roll if you're too young to die.

reacha
Starting Member

49 Posts

Posted - 2010-10-14 : 10:14:24

if i still modify my code with @x am getting the same error.

Please help me out with this while loop

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 10:17:46

Is it SQL Server 2000?
Then use SELECT @var = ... instead of SET

No, you're never too old to Yak'n'Roll if you're too young to die.

reacha
Starting Member

49 Posts

Posted - 2010-10-14 : 10:19:46

it is SQL server 2005

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 10:23:47

Please double click the error message and show us the line where it happens.
Or you can set display line numbers to true in SSMS in Options editor T-SQL to see the line number.

No, you're never too old to Yak'n'Roll if you're too young to die.

gmarut
Starting Member

8 Posts

Posted - 2010-10-14 : 11:02:42

Which line is #64?

Sachin.Nand

2937 Posts

Posted - 2010-10-14 : 13:13:08

quote:
Originally posted by gmarut

Which line is #64?

You have to find out what is there in line #64.
also did you modify x to @x in all the places of the query.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 13:13:46

quote:
Originally posted by gmarut

Which line is #64?

I've already asked that.

No, you're never too old to Yak'n'Roll if you're too young to die.

reacha
Starting Member

49 Posts

Posted - 2010-10-14 : 14:04:40

Sorry i about that

In this i am looping through #process temporary table

but i think the loop is not getting each and every pkgid

insert into #process(pkgid)
select top(1000) pkgid from AUDIT_MetricsInterp
where AuditStampCompleted is null
set @temp =(select count(pkgid) from #process)

WHILE (@temp != 0)
BEGIN
set @AuditStampCompleted = Null
set @userID = Null
exec [msp_UseridAuditStampCompleted]@pkgid,@AuditStampCompleted = @AuditStampCompleted output,@userID = @userid output
set @QueueEventID = Null
set @AuditstampQueueEntered = Null
exec [msp_EventIDAuditStampQueueEntered]@pkgid,@AuditstampQueueEntered = @AuditstampQueueEntered output,@QueueEventID = @QueueEventID output
set @AuditStampLocked = Null
set @AuditStampLocked = (SELECT [dbo].[mfun_AuditStampLocked] (@pkgid))

UPDATE AUDIT_MetricsInterp
SET USERID = @userid,QueueEventID = @QueueEventID,AuditStampLocked=@AuditStampLocked,
AuditStampCompleted = @AuditStampCompleted,AuditstampQueueEntered=@AuditstampQueueEntered
WHERE PKGID = @pkgid

set @temp = @temp-1
END

please help me out!!

Sachin.Nand

2937 Posts

Posted - 2010-10-14 : 14:06:49

Where are you setting the value of @pkgid?

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-14 : 14:07:36

i think the loop is not getting each and every pkgid

You know there is a top(1000) in your code?

No, you're never too old to Yak'n'Roll if you're too young to die.

reacha
Starting Member

49 Posts

Posted - 2010-10-14 : 14:12:25

i have 1000 packages.

i need to insert that each and every pkgid from #process into @pkgid

@pkgid is a variable in procedure and it is parameter in the calling procedures and function

Sachin.Nand

2937 Posts

Posted - 2010-10-14 : 14:29:48

quote:

i need to insert that each and every pkgid from #process into @pkgid

@pkgid is a variable in procedure and it is parameter in the calling procedures and function

So I am asking you WHERE is it's value being set in the code?

PBUH

reacha
Starting Member

49 Posts

Posted - 2010-10-14 : 14:44:36

sorry for all that confusion.

Finally i got the result

(select top(1)@pkgid = pkgid from #process)

Sachin.Nand

2937 Posts

Posted - 2010-10-14 : 14:47:34