Which version of SQL Server are you using? I remember having problems with some code we were writing in SQL 7 with CF 4.5.2. Here is the MSDN Reference to SET NOCOUNT ON, for those who are interested.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ set-set_3ed0.asp -- Scott Van Vliet Sempra Energy 555 W. 5th St., 21st Floor Los Angeles, CA 90013 Tel > 213.244.5205 Email > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> "Hello Stupid, and welcome to your crappy computer." - Strong Bad, HomestarRunner.com > -----Original Message----- > From: Smith, Matthew P -CONT(DYN) > [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 26, 2002 1:53 PM > To: CF-Talk > Subject: RE: help writing a stored proc > > > >>> Do forgot to SET NOCOUNT ON and OFF around the INSERT statement. > > I'm not sure, but I think you don't have to do that in a sp. > If you are > doing multiple SQL statements in one CFQUERY, then you have > to do the NO > COUNT ON/OFF thing. > > This works fine for us: > > IF NOT EXISTS (SELECT tablePK FROM tbl_personnel WHERE > tablePK = @tablePK) > BEGIN > INSERT INTO tbl_personnel (firstName, > mi, lastName, > eventStamp, lastAuthor) > VALUES (@firstName, @mi, @lastName, > @eventStamp, > @lastAuthor) > SET @person = @@IDENTITY > RETURN 1 > END > ELSE > BEGIN > UPDATE tbl_personnel > SET firstName = @firstName, > mi = @mi, > lastName = @lastName, > eventStamp = @eventStamp, > lastAuthor = @lastAuthor > WHERE tablePK = @tablePK > SET @person = @tablePK > RETURN 2 > END > > -----Original Message----- > From: Van Vliet, Scott [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 26, 2002 2:45 PM > To: CF-Talk > Subject: RE: help writing a stored proc > > > After the insert > > > > SELECT @@IDENTITY AS @newUserKey > > Do forgot to SET NOCOUNT ON and OFF around the INSERT > statement. The SET > NOCOUNT statment, when ON, will suppress the "(1 row(s) > affected)" message > which is returned. > > <snip> > > DECLARE @NewUserID > > SET NOCOUNT ON > INSERT INTO dbo.user_index( > username > ,password > ,access_level > ,active > ,email) > VALUES( > @email > ,@password > ,2 > ,0 > ,@email) > SELECT @@IDENTITY As @NewUserID > SET NOCOUNT OFF > > </snip> > > -- > Scott Van Vliet > Sempra Energy > 555 W. 5th St., 21st Floor > Los Angeles, CA 90013 > Tel > 213.244.5205 > Email > [EMAIL PROTECTED] > > "Hello Stupid, and welcome to your crappy computer." > - Strong Bad, HomestarRunner.com > > > > > > > > -----Original Message----- > > From: Smith, Matthew P -CONT(DYN) > > [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, June 26, 2002 12:20 PM > > To: CF-Talk > > Subject: RE: help writing a stored proc > > > > > > After the insert > > > > SELECT @@IDENTITY AS @newUserKey > > > > > > -----Original Message----- > > From: Brian J. LeRoux [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, June 26, 2002 1:55 PM > > To: CF-Talk > > Subject: help writing a stored proc > > > > I want to turn the following into a simple stored procedure: > > > > <CFLOCK TIMEOUT="60" NAME="stopDuplicates" TYPE="EXCLUSIVE"> > > > > <cfquery name="setNewUser" datasource="#request.dsn#" > > username="#request.username#" password="#request.pwd#"> > > INSERT INTO dbo.user_index > > (username, password, access_level, active, email) > > VALUES > > > > ('#structBillingInfo.email#','#structBillingInfo.userpwd#',2,0 > > ,'#structBilli > > ngInfo.email#') > > </cfquery> > > <cfquery name="getNewUser" datasource="#request.dsn#" > > username="#request.username#" password="#request.pwd#"> > > SELECT dbo.user_index.* > > FROM dbo.user_index > > ORDER BY user_ID DESC > > </cfquery> > > > > </cflock> > > > > <cfquery name="setNewUserInfo" datasource="#request.dsn#" > > username="#request.username#" password="#request.pwd#"> > > INSERT INTO dbo.user_info ( > > last_name, > > first_name, > > address, > > address2, > > city, > > state, > > country, > > shipping, > > pc_zip, > > user_ID > > ) VALUES ( > > '#structBillingInfo.carholdername#', > > '#structBillingInfo.carholdername#', > > '#structBillingInfo.strAddress#', > > '#structBillingInfo.strAddress#', > > '#structBillingInfo.city#', > > '#structBillingInfo.state#', > > '#structBillingInfo.country#', > > #structBillingInfo.shipping#, > > #structBillingInfo.ZipCode#, > > #get_new_user.user_ID# > > ) > > </cfquery> > > > > ################################################## > > > > So basically, I insert a new user, return their user_ID and > > populate it into > > a related table for all the user details. I don't know how to > > grab that > > return user_ID for populating via stored procedure. Any and > > all help is > > muchos appreciated! > > > > Thanks in advance! :) > > Brian > > > > > > > > > > > > > > > > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
