You can use an OUT parameter instead. See below for eg:
CREATE PROCEDURE getinsertnewssourceid @newssource varchar(256), @newssourcehref varchar(256), @id int OUT AS Set NOCOUNT ON; Select @id= newssourceid from newssources where newssource = @newssource and href = @newssourcehref; if @id is null begin Insert into newssources(newssource, href) values(@newssource, @newssourcehref); select @id= scope_identity(); end SET NOCOUNT OFF; GO ----- Original Message ----- From: "Michael Dinowitz" <[email protected]> To: "sql" <[email protected]> Sent: Thursday, April 29, 2010 6:13 AM Subject: Re: stored procedures not returning data > > 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:3339 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
