A couple of things could be going wrong
1) nvarchar ..... is used for Unicode text. Do you need Unicode text ?
2) when declaring a nchar or nvarchar when n is not specified in a data
definition or variable declaration statement, the default length is 1.
3) their is no need for quotes in your WHERE clause
4) You might want to upgrade your JOIN syntax ...some types of Joins in
SQL 7 and newer can product "Unpredictable Results" when you put the
join clause in the WHERE clause. And, personally I find that my queries
are a bit clearer when I use the ANSI join syntax
Your statement might read :
CREATE PROCEDURE CONSULTINFO
@user_n varchar (50),
@pass_w varchar (50),
@user_id int
AS
SELECT u.cons_id, u.user_n, u.pass_w,
c.first_n, c.last_n, c.phone, c.email,
c.city, c.state_id, c.level_id, c.resume,
c.enterdate, c.availability, c.type
FROM reg_users u ON c.cons_id = u.cons_id
INNER JOIN consultants c
WHERE u.pass_w = @pass_w AND
u.user_n=@user_n
Hope this helps.
-eric
------------------------------------------------
Common sense is genius dressed in its working clothes.
-- Ralph Waldo Emerson
Eric Barr
Zeff Design
(p) 212.714.6390
(f) 212.580.7181
-----Original Message-----
From: C Frederic Valone [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 21, 2001 11:58 AM
To: CF-Talk
Subject: stored proc trouble
I have created a stored proc in SQL 7 and for some reason cannot get it
to
"work" using the input params I need.
here is the proc:
CREATE PROCEDURE CONSULTINFO
@user_n nvarchar,
@pass_w nvarchar,
@user_id int
AS
select u.cons_id, u.user_n, u.pass_w,c.first_n, c.last_n, c.phone,
c.email,
c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability,
c.type
from reg_users u, consultants c
where u.pass_w = "@pass_w" and u.user_n="@user_n"
in the above I added the user_id field after for checking purposes. If
I run
the proc using the user_id as the in param the proc returns a row
correctly. (I
have also tried using single quotes in the where clause and no quotes,
neither
helped)
I have checked the values of the user_n and pass_w and they are correct
( a
cfquery using the same values returns the right info)
here is the cf code:
<cfquery name="TEST" datasource="jobs_db" dbtype="ODBC">
SELECT u.cons_id, u.user_n, u.pass_w,c.first_n, c.last_n, c.phone,
c.email, c.city, c.state_id, c.level_id, c.resume, c.enterdate,
c.availability,
c.type
FROM dbo.CONSULTANTS C, dbo.REG_USERS U
WHERE (U.USER_N='#user_n#' and U.PASS_W='#pass_w#')
and
(C.CONS_ID=U.CONS_ID)
</cfquery>
<!--- CFSTOREDPROC tag --->
<CFSTOREDPROC PROCEDURE="consultinfo"
DATASOURCE="jobs_db"
DEBUG>
<!--- CFPROCRESULT tags --->
<CFPROCRESULT NAME = cons>
<!--- CFPROCPARAM tags --->
<CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_VARCHAR"
VALUE="#user_n#" DBVARNAME=@user_n>
<CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_VARCHAR"
VALUE="#pass_w#" DBVARNAME=@pass_w>
<!--- this line was added for testing using the user_id and it then
returned
the information I needed --->
<!---<CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_INTEGER"
VALUE="1" DBVARNAME=@user_id><!---
<!--- Close the CFSTOREDPROC tag --->
</CFSTOREDPROC>
<h3>The Results Information test</h3>
<CFOUTPUT>#cons.recordcount#
<br>#cons.USER_N#
</CFOUTPUT>
I have also tried hard-coding the values of @used_n and @pass_w in both
the cf
and the stored proc with no better results.
Any ideas on what I am doing wrong?
thanks,
Frederic
--
/ \__
Frederic Valone ( @\___
Webmaster / O
American Kennel Club / (_____/
/_____/ U
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
