>>> 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
>
>
>
>
>
>
>
>
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
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
