Syntax. Try replacing with this bit:
create procedure dbo.sp_test (
@ratecode char(10),
@referringdomain varchar(100),
@affiliatename varchar(100) output
)
Note the parentheses and the change from 'out' to 'output'. Don't know if
that's your problem though.
----- Original Message -----
From: "Bosky, Dave" <[EMAIL PROTECTED]>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Thursday, July 03, 2003 9:18 AM
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
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
