What is the Return value coming from stored procedure 1

[ghbeers]

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

 

[Olaf Doschke]

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: [URL unfurl="true"]https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql[/url]

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.

 

[ghbeers]

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.