In your stored procedure you have to put the length of that column. Otherwise SQL Server defaults it to a length of 1.
Change that and rerun your stored procedure. Also, you can run this in SQL Query analyzer and see what you get back. That way you can determine if it is the procedure or CF that is causing the problem. HTH Clint ----- Original Message ----- From: "Adrian Lynch" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, June 02, 2003 11:20 AM Subject: RE: Correct syntax for stored procedures > I think, and somebody correct me if I am wrong, but if you don't have a > value in the varchar after the SP params it defaults to something quite > small. > > Try > > CREATE PROCEDURE spFindVisitor > @fname varchar(sizeIfField), > @lname varchar(sizeIfField) > > Also, put resultset="1" in the cfprocresult, I think it defaults to this > anyway, but worth a try. > > See if that helps > > Ade > > > -----Original Message----- > From: stas [mailto:[EMAIL PROTECTED] > Sent: 02 June 2003 17:05 > To: CF-Talk > Subject: Correct syntax for stored procedures > > > I am having a problem getting a result set. > > > Proc: > > CREATE PROCEDURE spFindVisitor > @fname varchar, > @lname varchar > > AS > SELECT ID as visitorid > FROM tblVisitor > WHERE first_name = @fname and last_name = @lname > GO > > I am calling it like this: > > <cfstoredproc procedure = "spFindVisitor" datasource="#request.datasource#"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" > dbvarname="@fname" value="holly"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" > dbvarname="@lname" value="bennet"> > <cfprocresult maxrows="1" name="qFindVisitor"> > </cfstoredproc> > > I am not getting any results back, even though I know a record is there. Is > my syntax correct? I've done this times and again under CF 4.5, but now I > am debugging an app under MX, MS SQL 2000. I tried both JDBC and MS SQL > drivers in MX, same results. > > Also, is there an advantage in using JDBC over MS SQL and vice versa? If > there is a link on MM's site, I'd greatly appreciate that. > > Much thanks > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
