Is there a switch in SQL 2000 that will prevent stored procedures from
returning data using scope_identity()? I'm using the following SP and
theoretically it should return a newssourceid no matter what. When a
new record is inserted, the newssourceid is never returned. The same
problem occurs when using the @@identity. In all cases, the record is
inserted into the DB but the PK is not returned. I've done just about
every experiment I can think of to get a result and no luck.
A further test shows that when a new record is inserted, two results
are returned rather than one. The first is the blank result of the
test select and the second has the new id value. Is there a way to
'blank out' the first select statement when inserting a record?
I appreciate the help with this. It's a massive pain that something so
simple should take such effort. :(
CREATE PROCEDURE getinsertnewssourceid
@newssource varchar(256), @newssourcehref varchar(256)
AS
Set NOCOUNT ON;
Select newssourceid
from newssources
where newssource = @newssource
and href = @newssourcehref;
if @@ROWCOUNT = 0
begin
Insert into newssources(newssource, href)
values(@newssource, @newssourcehref);
select newssourceid= scope_identity();
end
SET NOCOUNT OFF;
GO
--
Michael Dinowitz
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3336
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
