Ps. Guess I should have rewritten this for you for an out variable this is all you need- no dbvarname either- that goes with the in type. for type="in" you need dbvarname, value, cfsqltype. for type="out" use cfsqltype, variable. Also don't use the null=yes unless you KNOW for a fact that it will be null.
So your stored proc rewritten would be <CFSTOREDPROC PROCEDURE="sp_test" DATASOURCE="dsn"> <CFPROCPARAM DBVARNAME="@ratecode" TYPE="In" CFSQLTYPE="cf_sql_char" VALUE="#VariableType.qtip#"> <CFPROCPARAM DBVARNAME="@referringdomain" TYPE="In" CFSQLTYPE="cf_sql_varchar" VALUE="#VariableType.yahoo#"> <CFPROCPARAM TYPE="out" CFSQLTYPE="cf_sql_varchar" variable="AffiliateName"> </CFSTOREDPROC> #varType#=Form/session/attributes- whatever variable you are using. In your stored proc set that it can be null @Ratecode varchar(4)=Null, @referringdomain varchar(50)=Null, @AffiliateName varchar(30)=Null, etc -----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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
