Thanks Eric I'll try that when I get home.
Eric Barr wrote:
>
> 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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
