Ok, sorry I'm new to stored procs, heres what I've got now.. you'll see
below I'm just playing w/ dummy data to make it work. I guess I'm not quite
'getting it' yet. The variable returned via that function isn't working for
an insert.
On a psuedo-related note, can anyone here reccomend good resources /
tutorials / books for writing stored procs.?
Thanks again!
Brian
############################################################################
#
CREATE PROCEDURE [dbo].[spOnApprovedTransaction]
@first_name varchar(50),
@last_name varchar(50),
@address varchar(50),
@city varchar(50),
@state varchar(50),
@country varchar(50),
@shipping varchar(50),
@pc_zip varchar(50),
@user_ID varchar(50),
@email varchar(50),
-----------------------
@newUserKey int(4)
AS
INSERT INTO dbo.user_index (
username,
password,
access_level,
active,
email
) VALUES (
@email,
'undefined',
2,
0,
@email
)
SELECT @@IDENTITY AS @newUserKey
INSERT INTO dbo.user_index (
last_name,
first_name,
address,
address2,
city,
state,
country,
shipping,
pc_zip,
user_ID
) VALUES (
@first_name,
@first_name,
@first_name,
@first_name,
@first_name,
@first_name,
@first_name,
@first_name,
@first_name,
@newUserKey
)
-----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
