A couple of things to try: 1. you don't need paren around @company in your stored proc declaration.
2. try taking the %'s out of the SQL statement and passing in @company='%whateverl%' In other words include the % with the variable you pass in. It may very well be interpretting @company as literal since it is in quotes. CREATE PROCEDURE sp_test @Company nvarchar(100) AS SELECT * FROM Clients WHERE Company LIKE '@Company' return ----- Original Message ----- From: "Jeff Green" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, October 29, 2001 4:12 PM Subject: CFPROCPARAM doesnt seem to work > Hi all, > > Im working with stored procedures on MSSQL 7.0. I basically have everything > working right, but I cant get a string passed in to the stored proc > properly. > > My table (clients) has 2 records. I pass in the var @Company as a blank > string to return all recs, but I get nothing. I cant get it to return any > recs no matter what I assign @Company to. Its not breaking, its just not > getting any recs. > > The field "Company" in table "Clients" is data type of nvarchar(100), so I > made the stored proc var the same. By the way, I have successfully passed > in an int with no prob. > > Could the problem be cf is passing a varchar and not a nvarchar? > > Thanks for any help, > Jeff > > My stored proc: > > CREATE PROCEDURE sp_test > (@Company nvarchar(100)) > AS > SELECT * > FROM Clients > WHERE Company LIKE '%@Company%' > return > > > My code: > > <cfstoredproc datasource="#dsn#" procedure="sp_test" debug="Yes" > returncode="Yes"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@Company" > value=""> > <cfprocresult name="Companies" resultset="1"> > </cfstoredproc> > > <cfoutput> > recordcount: #Companies.RecordCount#<br> > </cfoutput> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com 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
