Preventing Return Values from Nested SPs.

Question

Preventing Return Values from Nested SPs.

fryere

Ten Centuries

Points: 1054
More actions
June 3, 2005 at 10:17 am

#167363

In the SP below I call an existing SP within the main SP to assign a FY to a variable with I use in other places in the Main SP. However when I execute the SP the return value from the nested SP is include in the return results. Is there anyway to prevent this?

DECLARE @FY as smallint
     --*** Call FY SP and Assign to FY
     exec @FY = fn_ProjectsGetCurrentFY

     IF @MTF_Key >0
     Begin
          SELECT Project_Num
          FROM tblProjects P, tblOrgs O
          WHERE @Mtf_Key = O.Org_Key
              AND O.Org_Name = P.MTF
              AND (p.Priority_FY is null OR P.Priority_Fy = @FY)

          RETURN
     End

Thanks
Fryere
fryere

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

A few things come to mind but those are all guesses. So if I'm wrong I'd like if you could post the whole code for all the objects that are part of this process.

Are you using .net?? >> is set nocount on applied at the top of the main proc?

have you tried Return 0 instead of just return (would be surprised if that was your problem)

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

I cannot get this to work yet, but I think this may be the approach. The thing is, sp_executesql need ntext, nvarchar, nchar to work and I think the EXECUTE statment may be causing the problem. I have tried CONVERT( nvarchar,...) in various locations, but I haven't had success.

Maybe Remi can see past my eyes... Or maybe this is not the approach... Good luck.

IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'spGetInteger')

AND OBJECTPROPERTY(id, N'IsProcedure')=1)

DROP PROC spGetInteger

GO

CREATE PROCEDURE spGetInteger

AS

SET NOCOUNT ON

SELECT 101

--------------------------------------------------------------------------------

DECLARE @FY datetime,

@sql varchar(100)

SET @sql = N'EXECUTE @OutPut = spGetInteger'

EXEC sp_executesql @sql, N'@OutPut nvarchar OUTPUT', @OutPut = @FY OUTPUT

IF CONVERT( integer, @FY) = 101

BEGIN

SELECT TOP 1 * FROM States

RETURN

END

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I've never had to use sp_executesql, so I wouldn't be of much help here .

fryere Ten Centuries Points: 1054 More actions · Answer 4

I am using MS Access for the front end, but It also happens in the query builder as well. I do not have set nocount on, which is probably the problem. If I still have problems I will post the entire SP.

Thanks.

fryere

fryere Ten Centuries Points: 1054 More actions · Answer 5

Let me repost the original problem again and be a little more descriptive this time. I am going to have a lot of stored procedures that the Result Set will be used as a data source on the front end. Many of the Result Sets will need to check a field (Priority_Fy) to see if matches the current fiscal year. I decided the best approach would be to set up a Stored Procedure (fn_ProjectsGetCurrentFY) that returned the current fiscal year and set it to a variable (@FY) in the calling stored procedure. This @FY variable will be used in the WHERE portion of the SQL that limits the Result Set (WHERE Priority_FY = @FY). The Problem is that I get two Results Sets. One for the Current FY and the One I want. How can I prevent the FY from being return in the calling Stored Procedure? Is there a better way to do this?

BTW SET NOCOUNT ON & RETURN(0) really didn't work for me.

Thanks.

Here is entire SP if it helps.

ALTER PROCEDURE dbo.pr_ProjectComboProjectCurrentRS

(@MTF_Key smallint = 0,

@RMC_Key smallint = 0)

AS

DECLARE @FY as smallint

--*** Call FY SP and Assign to FY

exec @FY = fn_ProjectsGetCurrentFY

IF @MTF_Key >0

Begin

SELECT Project_Num

FROM tblProjects P, tblOrgs O

WHERE @Mtf_Key = O.Org_Key

AND O.Org_Name = P.MTF

AND (p.Priority_FY is null OR P.Priority_Fy = @FY)

RETURN

End

IF @RMC_Key >0

Begin

SELECT Project_Num

FROM tblProjects P, tblOrgs O

WHERE @RMC_Key = O.Org_Key

AND O.Org_Name = P.RMC

AND (p.Priority_FY is null OR P.Priority_Fy = @FY)

RETURN

End

SELECT Project_Num

FROM tblProjects P

WHERE p.Priority_FY is null OR P.Priority_Fy = @FY

RETURN

fryere

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

RETURN

is the same as RETURN 0

Do you mean that you get 2 results set instead of one?

fryere Ten Centuries Points: 1054 More actions · Answer 7

Yes.

The First Result Set = 2005

The Second Result Set = 'Project1';'Project2';'Project_3';Etc

When I assign a Record Set to the Stored Procedure it returns:2005 because it is the first Result Set,instead of the project list that I want.

Thanks.

fryere

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Can you post the code you are using to call the sp along with the results?

Farrell Keough SSCoach Points: 17414 More actions · Answer 9

Try declaring @FY as a table variable. Then supply your @Variable with that value and it should not return the Fiscal Year value...

I wasn't born stupid - I had to study.