I have to leave early or I would try this

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

SQL Server 2000 Forums

SQL Server Development (2000)

I have to leave early or I would try this

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-03-16 : 15:39:56

Hello all. I have to take off here in a couple of minutes so I will make this quick.

If I do this within a stored procedure:

INSERT INTO tblProviderFacility_Effective
VALUES (
@intFacilityID,
@dtiStart,
@dtiEnd,
@txtNote,
@intOnHoldReasonID,
@intProviderID
)

and then, within the same stored proc, execute the following stored procedure,

EXEC qryUpdateFacility

RETURN SCOPE_IDENTITY()

Will my SCOPE_IDENTITY() be the key from my INSERT or will it become the key from the stored proc that I am executing?

Like I said, I would just throw this together and test it, but I have to run and pick up my kid for a Dr. appt.

Thanks!

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 16:16:10

You should put SCOPE_IDENTITY() into a variable immediately following the INSERT. You then RETURN @VarName.

If you leave it the way you have coded it, it'll return SCOPE_IDENTITY() from qryUpdateFacility if any INSERTs are performed in there.

Tara

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-03-17 : 09:10:43

As usual, thanks Tara! qryUpdateFacility will update an existing record and does not return anything (at least nothing declared).

I just got back in now, so I still have not had a chance to test this!

Thanks!

Aj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources