July 29, 2008 at 10:27 am
MrBaseball34 (7/29/2008)
[highlight="Yellow"]EXEC @ReturnID = rr_Insert_ptEncounterDetail[/highlight]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?
MrBaseball: I beleive that your stored procedures and calls are probably mixing two similar but different things.
The first is Return Codes: which is what you are trying to use in your highlighted example above. These are used by the form EXEC @int = spPROC(..) In order for this to work, the stored procedure being called must exit the procedure with a "RETURN x" statment, otherwise it returns 0. The called procs that you have showed us do not do this. (See Chris Morris's post for a good example of both the call and the return).
The second is stored procedure result set output. This is produced in the called stored procedure by a non-assignment SELECT statement. This is what your example stored procedures that are being called are doing, as in the "SELECT SCOPE_IDENTITY()" statment, above. These do nothing with the return code.
The only (normal) way to use "result set output" from a proc is to pipe it into a table using the INSERT..EXEC statment, as below. To do this with your proc, woudl be like this:
CREATE #RET (Val int)
INSERT into #RET
EXEC rr_Insert_ptEncounterDetail
SELECT Val From #RET
As for "why did it always work before?" Well, in all likelihood, your prior cases, probably had one of these two things changed so that the return method and the usage method matched.
[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 31, 2008 at 2:20 am
Sorry for the confusion due to my incomplete statement. Sp returns the return value when you Exclusively specify it. Otherwise it ALWAYS returns zero if executed successfully and retuirns an error number if not.
July 31, 2008 at 2:23 am
Dear Mrbaseball34...
Comming to your SP code for rr_Insert_ptEncounterDetail
CREATE PROCEDURE rr_Insert_ptEncounterDetail
@ptEncounterDetailID int
AS
BEGIN
INSERT INTO ptEncounter (EncounterDetailID) VALUES (@ptEncounterDetailID)
SELECT SCOPE_IDENTITY()
GO
If you are running the same code that you have posted, it STILL gives (atleast to me) the Selected SCOPE_IDENTITY() WITH @ReturnID = 0.
You can check this out. I think there is a difference between the code that you have posted and you are actually running on your machine.
One more thing...if you specify
RETURN SCOPE_IDENTITY()
instead of
SELECT SCOPE_IDENTITY()
than it WILL return the SCOPE_IDENTITY() value in the @ReturnID.
July 31, 2008 at 2:24 am
Ofcourse I know that the return value can be set in the SP and it returned accordingly IF it is retunned using RETURN.
SP also returns a vlaue and that is ZERO if you have NOT specified RETURN in your SP. (if i am not wrong...)
I dont claim to be the REAL DBA or anything like that. All i try is to help and LEARN...
Thanks and ENJOY T-SQL...:D
Atif Sheikh
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply