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
Get the mailserver that powers this list at
http://www.coolfusion.com
