July 28, 2008 at 1:14 pm
Am I doing something wrong here?
I am not that versed in the use of cursors and need some help.
' holder for identity value
DECLARE @ReturnID int
' inserted record and got identity value back
EXEC @ReturnID = rr_Insert_ptEncounterDetail
--
-- Now when looping through this cursor, @ReturnID is being sent as 0 to the
-- rr_Insert_ptEncounterEncounterDetail call instead of the value returned
-- in the above code. We KNOW it is returning a valid value.
--
IF (SELECT COUNT(*) FROM ptEncounterEncounterDetail WHERE EncounterID = @EncounterID) > 0
BEGIN
DECLARE @EncounterDetailID int
DECLARE curEE CURSOR FOR
SELECT EncounterDetailID
FROM ptEncounterEncounterDetail
WHERE EncounterID = @EncounterID;
OPEN curEE
FETCH NEXT FROM curEE INTO @EncounterDetailID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC rr_Insert_ptEncounterEncounterDetail @ReturnID, @EncounterDetailID
FETCH NEXT FROM curEE INTO @EncounterDetailID
END
CLOSE curEE
DEALLOCATE curEE
END
July 28, 2008 at 1:21 pm
Have you tested the value? Added a select or print command after the assignment to see what value it is?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 1:25 pm
one thing to note is the FETCH STATUS check, you should check if it is equal to 0 as opposed to <> -1 since it can return -2 as well, which states that the row fetched is missing.
however, i would check your variables as suggested before to see if values are actually being set.
also, i would see if there was a way to get around using a cursor in the first place but that is a different topic than what you are asking.
July 28, 2008 at 1:28 pm
As I said before, we KNOW it is has a valid value before the cursor begins looping.
I tried the = 0 .vs <> -1 but will do so again.
July 28, 2008 at 1:48 pm
If it starts with a value, it will have that value until something resets it, updates it, or it goes out of scope. I don't see anything in your code sample that would do any of those, so either it isn't being set, or there's something else happening that's not in the sample posted.
I just did this as a test:
declare @V1 int, @V2 int
select @v1 = 1
declare C cursor local static for
select 1
union all select 2
union all select 3
open c
fetch next from c
into @v2
while @@fetch_status = 0
begin
select @v1, @v2
fetch next from c
into @v2
end
It worked. @v1 came out as 1 in all selects.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 1:57 pm
We're still looking into the situation. Even when we changed to a "Select Into" it is still not keeping the value so it must be something besides the cursor.
July 28, 2008 at 3:57 pm
Ok, we've figured it out, it seems to be a bug in SQL2K5 and I will post reproducible code and our fix later.
July 28, 2008 at 5:45 pm
MrBaseball34 (7/28/2008)
Ok, we've figured it out, it seems to be a bug in SQL2K5 and I will post reproducible code and our fix later.
!!!
Wow. That sounds like a is a very significant bug. Could you please post the code to reproduce this as soon as possible? This is something that I want to know about right away. :w00t: At first I thought that it must be trying to return a value from a dynamic SQL Exec, but if it isn't then that is pretty important.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 29, 2008 at 4:58 am
I dont know about the bug found in the SQL Server 2005. But I need to clear a couple of things over here,
1. The variable @ReturnID id NOT the output parameter returned from your SP. It will always return 0 if the sp executes successfully.
2. If you have an output parameter in you SP, you have to declare that parameter as a normal local variable and pass it to the SP as a normal parameter with OUTPUT keyword.
For example...
The following variables are required to call this insert SP...
DECLARE @pCountryID int
DECLARE @pName nvarchar(100)
The following variable is to get the execution status of the called SP
DECLARE @rc int
Please note that the @pCountryID is the OUTPUT parameter of the SP and is declared in the scope.
Now lets call it
EXECUTE @rc = [db_LogBook_SC].[dbo].[uspInsertCountry]
@pCountryID OUTPUT
,@pName
Select @rc
Select @pCountryID
The @rc is 0.
The @pCountryID has the new Identity value returned by the SP.
thanks
Atif Sheikh
July 29, 2008 at 5:10 am
And I would be waiting for the SQL Server BUG. 😀
Atif Sheikh
July 29, 2008 at 7:32 am
Atif, yeah, I bet you're right! I should have seen that as well. He's got the variable being assigned as the return value from the proc, which is usually 0 or an error value. That's almost certainly the issue here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 7:39 am
Atif Sheikh (7/29/2008)
I dont know about the bug found in the SQL Server 2005. But I need to clear a couple of things over here,1. The variable @ReturnID id NOT the output parameter returned from your SP. It will always return 0 if the sp executes successfully.
EXEC @ReturnID = rr_Insert_ptEncounterDetail
when called in other procedures returns the valid value.
Here is how we are doing it in the abovbe procedure (simplified):
CREATE PROCEDURE rr_Insert_ptEncounterDetail
@ptEncounterDetailID int
AS
BEGIN
INSERT INTO ptEncounter (EncounterDetailID) VALUES (@ptEncounterDetailID)
SELECT SCOPE_IDENTITY()
GO
Then, can you explain why it returns the correct value when it is called separately?
Atif Sheikh (7/29/2008)
2. If you have an output parameter in you SP, you have to declare that parameter as a normal local variable and pass it to the SP as a normal parameter with OUTPUT keyword.
EXECUTE @rc = [db_LogBook_SC].[dbo].[uspInsertCountry]
@pCountryID OUTPUT
,@pName
Select @rc
Select @pCountryID
The @rc is 0.
The @pCountryID has the new Identity value returned by the SP.
This is the solution we came up with. We were not aware of the problems returning values
like this and as such will begin coding correctly. Apparently it is not a bug and is not well documented in BOL.
Keep in mind that the folks here are not *real* DBAs and their expertise is limited to what
they've done in the past in their own application, and it's not much, believe me. I have
done more in the past month of working here to make them aware of problems with their
SQL and there will more than likely be plenty more changes.
Thanks...
July 29, 2008 at 7:44 am
Atif Sheikh (7/29/2008)
1. The variable @ReturnID id NOT the output parameter returned from your SP. It will always return 0 if the sp executes successfully.
No, that is not true Atif. @ReturnID is assigned the Return Code of the stored procedure that is being called. Return Codes are NOT always 0 if sucessful, that is a convention and it depends entirely on how the stored procedure was written.
You can write a stored procedure to return any Return Code integer value that you want, which is what I am assuming that they have done here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 29, 2008 at 7:58 am
rbarryyoung (7/29/2008)
Atif Sheikh (7/29/2008)
1. The variable @ReturnID id NOT the output parameter returned from your SP. It will always return 0 if the sp executes successfully.No, that is not true Atif. @ReturnID is assigned the Return Code of the stored procedure that is being called. Return Codes are NOT always 0 if sucessful, that is a convention and it depends entirely on how the stored procedure was written.
You can write a stored procedure to return any Return Code integer value that you want, which is what I am assuming that they have done here.
create procedure dbo.ReturnTest
as
return 10
go
declare @retval INT
exec @retval = dbo.ReturnTest
print @retval
-- 10
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 29, 2008 at 10:10 am
Chris Morris (7/29/2008)
rbarryyoung (7/29/2008)
You can write a stored procedure to return any Return Code integer value that you want, which is what I am assuming that they have done here.create procedure dbo.ReturnTest
as
return 10
go
declare @retval INT
exec @retval = dbo.ReturnTest
print @retval
-- 10
Good example, Chris.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply