What about putting single quotes around A.AffiliateName since it's a string?
SELECT @affiliateID = A.AffiliateID, @ratecodeID = B.RateCodeID, @AffiliateName = 'A.AffiliateName' ----- Original Message ----- From: "Bosky, Dave" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, July 03, 2003 10:12 AM Subject: RE: Returning a value from a stored procedure. > The problem seems to be in this line: > SELECT @affiliateID = A.AffiliateID, @ratecodeID = B.RateCodeID, > @AffiliateName = A.AffiliateName > > > This worked fine: > SELECT * > FROM dbo.tbl_usave_affiliates A INNER JOIN > dbo.tbl_usave_affiliates_ratecodes B ON A.AffiliateID = B.AffiliateID > WHERE (A.ReferringDomain = '1.1.1.1') AND (B.RateCode = 'HTC69') AND > (B.ExpirationDate >= getdate()) > > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 03, 2003 11:05 AM > To: SQL > Subject: RE: Returning a value from a stored procedure. > > > Then it must be your original SELECT which is failing....try and do a select > with values you know exist and performa ALL of your debugging in QA.... > > If you can post some sample records it would also be helpful > > > > > -----Original Message----- > From: Bosky, Dave [mailto:[EMAIL PROTECTED] > Sent: 03 July 2003 15:52 > To: SQL > Subject: RE: Returning a value from a stored procedure. > > > I commented out the insert statement and printed the @AffiliateName variable > which was empty. Must be something simple but I'm stuck.... > > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 03, 2003 10:46 AM > To: SQL > Subject: RE: Returning a value from a stored procedure. > > > hmm.. the logic looks sound. Is it entering anything via the INSERT? do a > print before the INSERT and comment out the insert. > > Also, set the @AffiliateName = something you know is in the DB.....it looks > like a data issue and not code if QA is causing it to freak. > > > > -----Original Message----- > From: Bosky, Dave [mailto:[EMAIL PROTECTED] > Sent: 03 July 2003 15:40 > To: SQL > Subject: RE: Returning a value from a stored procedure. > > > Nope. It prints out an empty string in query analyzer. > > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 03, 2003 10:37 AM > To: SQL > Subject: RE: Returning a value from a stored procedure. > > > Does it run AOK in Query Analyser? Also, its a good idea not to name your > Stored Procedures with the prefix sp_ these are best reserved for System > procs. Although there is not problem at all with using SP per say, it does > mean the SQL engine will look in the master db before looking in your DB for > he actual proc which can cause performance issues on heavy load servers. > > Put a PRINT @AffiliateName at the bottom and also change your proc to ALTER > (though im assuming you have done that!) > > > > -----Original Message----- > From: Bosky, Dave [mailto:[EMAIL PROTECTED] > Sent: 03 July 2003 15:18 > To: SQL > 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
