RE: DBD::ODBC SQL Server Stored Procedure

[Gorrebeeck, Robert]

Let me add one more bit of information
I am calling another stored procedure from the original stored procedure.  This 
is when I lose my values.
If the account loc variable is Null I set the ErrorId -1 - and that gets 
returned to the perl script - it is when I call the CreateFeed stored procedure 
that I lose the output values

The stored proc (as is it bellows I know is incorrect but just for illustration 
purposes)

See below

ROCEDURE [CreateReferralDataProcess]
    @TrackingId      NUMERIC(20,0),
    @RefID              CHAR(36) OUTPUT,
    @RefErrorID         CHAR(36) OUTPUT
AS
SET NOCOUNT ON

SELECT    @AccountLoc         = ACCOUNT_LOC
FROM    ACCOUNT_DETAILS ACCOUNT_DETAILS
WHERE  ID = @TrackingID

--if we dont have an account loc then error out and return to the perl script
IF @AccountLoc IS NULL OR @AccountLoc <=0
BEGIN

    SELECT @ErrorID -1

    SELECT @RefErrorID = CONVERT(CHAR(36), @ErrorID)
    SELECT @RefID = CONVERT(CHAR(36), -1)
    RETURN
END
ELSE
BEGIN

    --Now that we have all of the account info we can start to create the 
referral
    EXEC ChangePoint.dbo.CreateFeed  @AccountLoc,
                                                        @ReferralID             
OUTPUT,
                                                        @ErrorID                
OUTPUT

    --Lets check the return value of the ErrorId
    IF @ErrorID > 0
    BEGIN

        SELECT @RefErrorID = RTRIM(CONVERT(CHAR(36), @ErrorID))
    END
    ELSE
    BEGIN
        --This means that we got a valid referral id back
        --Convert so we can return the value to our script
        SELECT @RefID = RTRIM(CONVERT(CHAR(36), @ReferralID))
    END
END
RETURN
SET NOCOUNT OFF



Robert Gorrebeeck
Sr. Apps Dev Analyst
Coventry Workers Comp Services
Solutions to Restore Health and Productivity
Office: (972) 473-2942
gorrebeec...@cvty.com<mailto:gorrebeec...@cvty.com>
www.coventrywcs.com<http://www.coventrywcs.com>

From: Gorrebeeck, Robert
Sent: Wednesday, August 29, 2012 2:02 PM
To: beginners@perl.org
Subject: DBD::ODBC SQL Server Stored Procedure

All

I am having an issue with retrieving output parameters from a SQL Server stored 
procedure.

Here is the definition of the stored procedure

PROCEDURE [TestProcess]
    @Id  NUMERIC(20,0),
    @RefID          CHAR(36) OUTPUT,
    @ErrorID        INT OUTPUT

And here is my perl code
#bind the input and output parameters

$sth->bind_param(1, $clm_tracking_id);
$sth->bind_param_inout(2, \$ID, DBI::SQL_CHAR);
$sth->bind_param_inout(3, \$ErrorId, DBI::SQL_INT);
$sth->execute();

$sth->finish;

print "$ID\n";
print "$ErrorId\n";

When I run the stored procedure by itself , I get values back, but when I run 
it from my perl script, the values are empty.
Not sure what is going on - any advice would be greatly appreciated

Thanks

Robert Gorrebeeck



Email Confidentiality Notice: The information contained in this transmission is 
confidential, proprietary or privileged and may be subject to protection under 
the law, including the Health Insurance Portability and Accountability Act 
(HIPAA).

The message is intended for the sole use of the individual or  entity to whom 
it is addressed.  If you are not the intended recipient, you are notified that 
any use, distribution or copying of the message is strictly prohibited and may 
subject you to criminal or civil penalties.  If you received this transmission 
in error, please contact the sender immediately by replying to this email and 
delete the material from any  computer.