Would help if I start reading the whole email before responding. I'm having a rough day here....
SET @AffiliateName = '' Why did you do this in your stored proc?? Won't this automatically always set it to ''? -----Original Message----- From: Bosky, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2003 10:16 AM To: CF-Talk Subject: RE: Returning value from a stored procedure? 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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
