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.