Thank you. I didn't know that. I'm actually new to stored procedures and have my nose in a book now. :)
----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, July 03, 2003 10:05 AM Subject: RE: Returning a value from a stored procedure. > OUT and OUTPUT are the same and will do the same job. > > -----Original Message----- > From: Dina Hess [mailto:[EMAIL PROTECTED] > Sent: 03 July 2003 15:50 > To: SQL > Subject: Re: Returning a value from a stored procedure. > > > Syntax. Try replacing with this bit: > > create procedure dbo.sp_test ( > @ratecode char(10), > @referringdomain varchar(100), > @affiliatename varchar(100) output > ) > > Note the parentheses and the change from 'out' to 'output'. Don't know if > that's your problem though. > > ----- Original Message ----- > From: "Bosky, Dave" <[EMAIL PROTECTED]> > To: "SQL" <[EMAIL PROTECTED]> > Sent: Thursday, July 03, 2003 9:18 AM > Subject: Returning a value from a stored procedure. > > > > Are there any SQL guru's that can help me with this stored procedure? > > I'm trying to return '@AffiliateName' but it always returns ''. > > I know the query matches one record but the value is never saved to the > out > > variable '@AffiliateName'. > > > > ------------------------------------------------------------------ > > <CFSTOREDPROC PROCEDURE="sp_test" DATASOURCE="dsn"> > > <CFPROCPARAM DBVARNAME="@ratecode" TYPE="In" > > CFSQLTYPE="cf_sql_char" null="no" VALUE="qtip"> > > <CFPROCPARAM DBVARNAME="@referringdomain" TYPE="In" > > CFSQLTYPE="cf_sql_varchar" null="no" VALUE="yahoo"> > > <CFPROCPARAM DBVARNAME="@AffiliateName" TYPE="out" > > CFSQLTYPE="cf_sql_varchar" variable="AffiliateName"> > > </CFSTOREDPROC> > > ------------------------------------------------------------------ > > CREATE PROCEDURE dbo.sp_test > > @ratecode char (10), > > @referringdomain varchar (100), > > @AffiliateName varchar (100) OUT > > AS > > DECLARE @affiliateID int, @ratecodeID int > > SET @AffiliateName = '' > > SELECT @affiliateID = A.AffiliateID, > > @ratecodeID = B.RateCodeID, > > @AffiliateName = A.AffiliateName > > FROM dbo.tbl_A A > > INNER JOIN dbo.tbl_B B ON A.AffiliateID = B.AffiliateID > > WHERE (A.ReferringDomain = '@referringdomain') > > AND (B.RateCode = '@ratecode') > > AND (B.ExpirationDate >= getdate()) > > IF @AffiliateName <> '' > > INSERT INTO dbo.tbl_C (affiliateID, ratecodeID) > > VALUES (@affiliateID,@ratecodeID) > > ------------------------------------------------------------------ > > > > Regards, > > Dave Bosky > > > > > > > > HTC Disclaimer: The information contained in this message may be > privileged and confidential and protected from disclosure. If the reader of > this message is not the intended recipient, or an employee or agent > responsible for delivering this message to the intended recipient, you are > hereby notified that any dissemination, distribution or copying of this > communication is strictly prohibited. If you have received this > communication in error, please notify us immediately by replying to the > message and deleting it from your computer. Thank you. > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
