return value from a stored procedure.

[sknyppy]

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'.

The problem seems to be in the line:
SELECT @affiliateID = A.AffiliateID,
@ratecodeID = B.RateCodeID,
@AffiliateName = A.AffiliateName


------------------------------------------------------------------
<CFSTOREDPROC PROCEDURE=&quot;sp_test&quot; DATASOURCE=&quot;dsn&quot;> <CFPROCPARAM DBVARNAME=&quot;@ratecode&quot; TYPE=&quot;In&quot;
CFSQLTYPE=&quot;cf_sql_char&quot; null=&quot;no&quot; VALUE=&quot;qtip&quot;>
<CFPROCPARAM DBVARNAME=&quot;@referringdomain&quot; TYPE=&quot;In&quot;
CFSQLTYPE=&quot;cf_sql_varchar&quot; null=&quot;no&quot; VALUE=&quot;yahoo&quot;>
<CFPROCPARAM DBVARNAME=&quot;@AffiliateName&quot; TYPE=&quot;out&quot;
CFSQLTYPE=&quot;cf_sql_varchar&quot; variable=&quot;AffiliateName&quot;>
</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)
------------------------------------------------------------------
Thanks..

 

[roccorocks]

I know this might seem kind of simple, but I always thought you would return the value like this:

RETURN @AffiliateName

I could be mistaken, but just a thought.

Rocco

 

[jpbrassard]

No, RETURN can only return numeric values (int, smallint, etc). If you need to return a non-numeric value, of which @AffiliateName is one (varchar), then you must declare a parameter on the proc as OUTPUT, and set that parameter before exiting the proc. I thought you could shorten that OUTPUT declaration to OUT without consequences, but maybe you can't.

Also, it looks like you're accessing this proc thru ColdFusion (I'm guessing, having never really used CF before, but knowing it uses it's own special tags and then seeing markup tags with a &quot;CF&quot; at the beginning... it's probably a good guess). Maybe ColdFusion doesn't play well with the OUTPUT parameter declaration? Maybe it has to be specifically OUTPUT, rather than just &quot;out&quot;?

Don't know. You're proc looks fine - my guess is it's your interface choking on it somewhere.

HTH,
jp

 

[philhege]

A way around this: CFPROCPARAMs are finicky, so construct the SP to output a recordset. You're close enough already:

re-construct the SP so that

SELECT A.AffiliateID as affiliateID,
B.RateCodeID as ratecodeID,
A.AffiliateName as 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())

is the last statement in the SP.

call the sp from a CFQUERY tag:

<CFQUERY name=&quot;affiliatereturn&quot; datasource=&quot;mydsn&quot;>
exec dbo.sp_test '#ratecode#','#referringdomain#',''
</cfquery>

Your values should be the columns in the affiliatereturn query.


HTH,


Phil Hegedusich
Senior Web Developer
IIMAK
<a href=&quot;

http://www.iimak.com&quot;>http://www.iimak.com</a>

-----------
Boy howdy, my Liberal Studies degree really prepared me for all of this....

 

[coco86]

Did you ever figure this out? I ask because I'm having a similar problem. My SP looks like this:

CREATE Procedure TestCopy
  @ID int
, @autonum int OUTPUT
AS
 begin tran
 insert tblPO (OrderID, CompanyID, VendorID, 
               OrderedBy,InputAmount, Shipto, ShipVia, 
               Terms, FOB, TaxID, ContactName, Phone, 
               Fax, POComments)
 Select OrderID, CompanyID, VendorID, OrderedBy, 
        InputAmount, Shipto, ShipVia, Terms, 
        FOB, TaxID, ContactName, Phone, Fax, 
        POComments
 from tblPO where POID = @ID
 set @autonum = @@identity
 Insert tblPOItems (POID, ItemName, ItemQty, ItemDescrip, 
                    ItemPrice) 
 select @autonum, ItemName, ItemQty, ItemDescrip, 
        ItemPrice 
 from tblPOItems where POID = @ID
 commit tran

I'm calling it from VBA with this

Public Function copyPO(ByVal OldID As Long) As Long
    Dim NewID As Long
    Dim conn As New ADODB.Connection
    Dim cmdStoredProc As ADODB.Command
    
    conn.ConnectionString = MS_CONN_STRING
    conn.Open
    Set cmdStoredProc = New ADODB.Command
    Set cmdStoredProc.ActiveConnection = conn
    
    With cmdStoredProc
        .CommandText = "testcopy"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@ID", 
                       adInteger, adParamInput, , OldID)
        .Parameters.Append .CreateParameter("@autonum", 
                       adInteger, adParamOutput, , NewID)
        .Execute
    End With
    
    copyPO = NewID
End Function

but NewID always ends up 0. I know the value of @autonum is getting set because the line items get added correctly with the new PO number.

I get a null value for @autonum when I try to run the SP in Query Analyer.

-Coco

 

[coco86]

I made the following change:

    With cmdStoredProc
        .CommandText = "testcopy"
        .CommandType = adCmdStoredProc
        Set p1 = .CreateParameter("@ID", adInteger, 
                      adParamInput, , OldID)
        Set p2 = .CreateParameter("@autonum", adInteger, 
                      adParamOutput, , Null)
        .Parameters.Append p1
        .Parameters.Append p2
        .Execute
    End With
    
    copyPO = p2.Value

and I finally got the parameter value. I don't know how that would translate to Cold Fusion but hopefully it helps.

-Coco