Hi Dave, Thanks for the response. I think the question I want to have answered is why?
It is clear that MS SQL is not limited to positional notation. If I call the same procedure through an EXEC call, like so: EXEC qry_test @user_id = 5000, @username = 'haggerty', @password = 'haggerty123' I can pass the variables in any order I want. Why does it matter for Cold Fusion to pass them in order? Thanks, Mike -----Original Message----- From: Bosky, Dave [mailto:[EMAIL PROTECTED]] Sent: Monday, December 31, 2001 4:45 PM To: CF-Talk Subject: RE: MSSQL Stored Proc and Named Notation List your parameters in the order you send them. @user_id int = NULL @username varchar(30) = '' @password varchar(30) = '' Dave -----Original Message----- From: Michael Haggerty [mailto:[EMAIL PROTECTED]] Sent: Monday, December 31, 2001 4:42 PM To: CF-Talk Subject: MSSQL Stored Proc and Named Notation Hi, I am using MS SQL Server 7.0 and CF 5.0 and I am having trouble with a call to a stored procedure. The procedure is detailed below, and when I try to call it using the following code, it blows up: <cfstoredproc datasource="#application.transact_database#" procedure="qry_test" debug> <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@user_id" value=form.user_id NULL="No"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@username" value="some.username" NULL="No"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@password" value=some.password NULL="No"> <cfprocresult name="qry_test" resultset="1"> </cfstoredproc> When I move the @user_id procparam to the end of the parameters list, everything works fine. My question is, I thought that by specifying the name of the parameter you are passing in via the dbvarname property, you were not limited to positional notation. Why does the order of my variables affect my call to the stored procedure? Thanks, Mike ALTER PROC qry_test @username varchar(30) = '', @password varchar(30) = '', @user_id int = NULL AS IF @user_id IS NOT NULL BEGIN SELECT user_id, username, password, fname, lname, active_ind, prefix, mi FROM ref_users WHERE user_id = @user_id END ELSE BEGIN SELECT 'haggerty' fname, 'haggerty' lname, @username passed_username SELECT user_id, fname, lname, getdate() time FROM ref_users WHERE active_ind = 1 AND username = @username AND password = @password AND username <> '' AND password <> '' END -----Original Message----- From: Bryan Stevenson [mailto:[EMAIL PROTECTED]] Sent: Monday, December 31, 2001 12:46 PM To: CF-Talk Subject: Hey Michael...what's going on? I'm getting a bunch of ScanMail messages back from the list saying my messages contain "sensitive content" and have been blocked!! What did I say? I'm pretty sure I didn't disclose any national secrets, use bad language, or say bad things about the list ;-) Any ideas? TIA Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] --------------------------------------------------------- Allaire Alliance Partner www.allaire.com ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb 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
