INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

What is the Return value coming from stored procedure

What is the Return value coming from stored procedure

(OP)
I have a stored procedure (see sample below) that is a report. It runs correctly, but it always has an additional piece of output. Return Value of zero. (See attachment).

When I put this stored procedure into a Entrinsik Informer reporting tool, it's not liking that return value. Is there some way I can prevent it from prevent it from trying to return something to the reporting tool?


USE [coll18_test]
GO
/****** Object: StoredProcedure [dbo].[TEST_DEG_OFFERED2] Script Date: 8/7/2017 8:53:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TEST_DEG_OFFERED2]
@term CHAR(7)
AS
BEGIN
SET NOCOUNT ON;

select
AC.ACAD_INSTITUTIONS_ID,
AC.ACAD_TERM,
AC.ACAD_DIVISION,
APL.ACPG_DEPTS,

etc., etc.,

from ACAD_CREDENTIALS AC

a bunch of left joins

WHERE AC.ACAD_TERM = @term
AND AC.ACAD_INSTITUTIONS_ID IN ('0000001','0000640')
END
GO











RE: What is the Return value coming from stored procedure

You have to look for the problem in the Entrinsik Informer reporting tool.

MSSQL stored procs only return an error level/status as a scalar value, if at all, then 0 will mean OK, no error. Otherwise it returns the resultset(s) it generates.

Just see the reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

Quote (Docs)

Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

Then your stored proc (or the stored proc programmed for you) can have a RETURN statement explicitly returning something. See the section "Returning Data Using a Return Code".

Well, look at the code of your stored proc, we can't see that from here...

The only other reason you may get scalar output values, is from parameters defined as OUT parameters. They can also be both IN and OUT. When theses parameter variables are modified by stored Proc code, that also gets back to the caller, but not as the return value, it simply modifies the bound client language variables. I doubt this is at work here.

Bye, Olaf.


RE: What is the Return value coming from stored procedure

(OP)
Thank you Olaf. I am working with a tech from Entrinsik who acknowledges there may be a bug on their side. I now know for sure that what the stored proc is doing is what it should be doing. This is not a problem with other reporting tools into which I insert stored procs. Thank you again.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close