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
