Hi all,
I have this stored procedure to insert a user in the users table and return
a couple of variables.
It checks if there is already a record with the give email address. If not,
generates a password and inserts the record (email address is unique).
(I don't like the way it generates the password either, but it wasn't my
idea - I didn't write most of the stored procedure - blame it on the DBA!)
Anyway, the stored procedure returns the created password and the variable
"ExistingEmail" to tell me if the record was not inserted because of an
existing record with the same email.
What is happening is:
Try to insert with an email that already exists in the database, receive
expected results - ExistingEmail is "True".
Try to insert with new email, it inserts alright - but returns unreadable
values for Password and ExistingEmail.
When I set ExistingEmail to be a varchar, it returns 255 unreadable
characters. If I set it to be an integer, it returns less of the same
characters.
Does anyone know why I get that?
We're using CF4.5 on Windows NT. The database is on SQL 2000, I believe.
Here's the stored procedure:
if exists (select * from sysobjects where id =
object_id(N'[dbo].[proc_blahblah]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[proc_blahblah]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.proc_blahblah
-- These Parameters provide information for the blah table.
@Email varchar(100),
@MemberFirstName varchar(100),
@MemberLastName varchar(100),
-- These OUTPUT Parameters return information to the Caller about
-- the blah row inserted.
-- ExistingEmail tells if there is already a record with the current email
address.
@Password varchar(6) OUT,
@ExistingEmail varchar(5) OUT
AS
--Get current date/time for datestamp value.
DECLARE @currdate datetime,
@MemberId int
SELECT @currdate = getdate()
-- Preset return values.
SELECT @Password = ' '
SELECT @ExistingEmail = 'False'
IF not EXISTS
(SELECT Email
FROM blah
WHERE blah.Email = @Email
)
BEGIN
SELECT @Password = left(ltrim(rtrim(@MemberLastName)),6)
-- inserts a row into the blah table
INSERT blah
(Email,
MemberFirstName,
MemberLastName,
Password,
DateLastUpdated)
VALUES
(@Email,
@MemberFirstName,
@MemberLastName,
@Password,
@currdate)
-- Quit if error occurred.
IF @@ERROR <> 0
GOTO Proc_Err
--Save MemberId
SELECT @MemberId = @@IDENTITY
-- insert row into blah table
-- this could return OUT parameter to notify success
-- exec proc_blahblah @MemberId
--return to caller.
RETURN
END
ELSE
BEGIN
SELECT @Password = ' '
SELECT @ExistingEmail = 'True'
--return to caller.
RETURN
END
--error routine
Proc_Err:
BEGIN
SELECT @Password = ' '
SELECT @ExistingEmail = 'False'
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------
-----------------------------
Now here's the code:
<cfset variables.Email = trim(form.Email)>
<cfset variables.MemberFirstName = trim(form.MemberFirstName)>
<cfset variables.MemberLastName = trim(form.MemberLastName)>
<!--- This stored procedure checks if there is an existing record with the
given email and if not, inserts record in table --->
<!--- Returns ExistingEmail and Password --->
<cfstoredproc procedure="proc_FirmMembersICSAIns" datasource="LWP">
<cfprocparam type="IN" dbVarName="Email" value="#variables.Email#"
CFSQLType = "CF_SQL_VARCHAR">
<cfprocparam type="IN" dbVarName="MemberFirstName"
value="#variables.MemberFirstName#" CFSQLType = "CF_SQL_VARCHAR">
<cfprocparam type="IN" dbVarName="MemberLastName"
value="#variables.MemberLastName#" CFSQLType = "CF_SQL_VARCHAR">
<cfprocparam type="OUT" dbVarName="Password"
variable="AssignedPassword" CFSQLType = "CF_SQL_VARCHAR">
<cfprocparam type="OUT" dbVarName="ExistingEmail"
variable="ExistingEmail" CFSQLType = "CF_SQL_VARCHAR">
</cfstoredproc>
<cfoutput>
Password '#AssignedPassword#', ExistingEmail '#ExistingEmail#'
</cfoutput>
TIA,
Claudia
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.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
