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 ______________________________________________________________________ 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=coldfusiona 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
