I AM A MUPPET
it was staring me in the face all the time... Your select is performing a
join and in the WHERE clause you are using GetDate() and the condition is
>= for records in the DB....this will NEVER EVER be true as SQL Server looks
at the WHOLE string of a date - including the Time which means your
recordset will be empty.
If you explicitly enter a date - it will work no problems thus :
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 >= '10/10/2002'
IF @AffiliateName <> ''
INSERT INTO dbo.tbl_C (affiliateID, ratecodeID)
VALUES (@affiliateID,@ratecodeID)
-----Original Message-----
From: Bosky, Dave [mailto:[EMAIL PROTECTED]
Sent: 03 July 2003 16:21
To: SQL
Subject: RE: Returning a value from a stored procedure.
I have the insert statement commented out until I figure out why the select
statement is failing.
Something with the line.
SELECT @affiliateID = A.AffiliateID, @ratecodeID = B.RateCodeID,
@AffiliateName = A.AffiliateName
-----Original Message-----
From: Andy Ewings [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 03, 2003 11:21 AM
To: SQL
Subject: RE: Returning a value from a stored procedure.
So is a record being inserted into the table as your logic is doing in the
SP?
-----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
