I solved the problem by having 2 cfprocresult tags and checking whether there was a returned value in the first result or the second. If the first, then it's an old record. If the second, it's new. This feels like a hack and I would love to know if it is possible to have the SP return only one record.
Thanks -- Michael Dinowitz On Thu, Apr 29, 2010 at 5:32 AM, Michael Dinowitz <[email protected]> wrote: > 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 > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:3337 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
