There's still a problem with the sp because when I execute it in query analyzer it prints a empty string. Some reason the query value is not getting put in the variable.
-----Original Message----- From: Janine Jakim [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2003 10:08 AM To: CF-Talk Subject: RE: Returning value from a stored procedure? Get rid of the null=yes. That is setting it to ''. -----Original Message----- From: Bosky, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2003 10:06 AM To: CF-Talk Subject: Returning 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" null="yes" variable="AffiliateName"> </CFSTOREDPROC> ------------------------------------------------------------------ CREATE PROCEDURE dbo.sp_test @ratecode char (10), @referringdomain varchar (100), @AffiliateName varchar (100) = NULL 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=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
