assigning value from sproc inside another sproc

Question

assigning value from sproc inside another sproc

SSC Eights!

Points: 846

September 9, 2022 at 4:36 pm

I have a customer that wants to pull some information on all applicants into an excel spreadsheet so that she can see all information on everyone from one place. My issue is that the information she wants requires the stored procedure that will display the results to get some values from another stored procedure and then pass those values into a SQL Function to get the final value that she wants to see, and I need this done in a single query so that my C# class can return an enumerable list so that I can then build the spreadsheet.

Where I am having a problem is on these 5 lines. 4 of them call a stored procedure GetTheStanine which will return a standard nine value, the other line calles a udf CalculateDependability to get an alpha rating and I will then assign some values back to my table variable.

SET @Energy = EXEC GetTheStanine @AppID,1,8 
SET @Flex = EXEC GetTheStanine @AppID,1,9 
SET @Develop = EXEC GetTheStanine @AppID,1,12
SET @Distort = EXEC GetTheStanine @AppID,1,18
SELECT @Depend = dbo.CalculateDependability(@Energy,@Flex,@Develop)

here is my stored procedure

CREATE PROCEDURE DataOnApplicants

@StartDate date,
@EndDate date

AS

SET NOCOUNT ON

DECLARE @Temp Table
(
ApplicationID nvarchar(125),
FirstName nvarchar(30),
LastName nvarchar(50),
ApplicationStartDate datetime,
ApplicationEndDate datetime null,
Score int,
Finished bit,
JobTitle nvarchar(100),
Dependability char(1) null,
Distortion int null,
Tid int identity
)

INSERT @Temp
SELECT 
A.ApplicationID,
U.FirstName,
U.LastName,
A.ApplicationStartDate,
A.ApplicationEndDate,
A.AcuityScore,
A.Finished,
J.JobTitle,
NULL,
NULL
FROM
dbo.AspNetUsers U INNER JOIN
dbo.Applicants A ON U.Id = A.UserId INNER JOIN
dbo.Jobs J ON A.JobID = J.JobID
WHERE
CAST(A.ApplicationStartDate AS date) BETWEEN @StartDate and @EndDate

DECLARE @Start int, @Stop int, @AppID nvarchar(125), @Energy int, @Flex int, @Develop int, @Distort int, @Depend char(1)

SET @Start = 1
SELECT @Stop = COUNT (*) + 1 FROM @Temp
WHILE @Start < @Stop
BEGIN
SELECT @AppID = ApplicationID FROM @Temp WHERE Tid = @Start
IF EXISTS(SELECT * FROM dbo.EvalData WHERE ApplicationID = @AppID AND Completed != '0')
BEGIN
SET @Energy = EXEC GetTheStanine @AppID,1,8 
SET @Flex = EXEC GetTheStanine @AppID,1,9 
SET @Develop = EXEC GetTheStanine @AppID,1,12
SET @Distort = EXEC GetTheStanine @AppID,1,18

SELECT @Depend = dbo.CalculateDependability(@Energy,@Flex,@Develop)

UPDATE @Temp SET Dependability = @Depend, Distortion = @Distort, WHERE tid = @Start

END
SET @Start = @Start + 1
END

SELECT
FirstName,
LastName,
ApplicationStartDate,
ApplicationEndDate,
Score,
Finished,
JobTitle,
Dependability,
Distortion
FROM
@Temp
ORDER BY 
ApplicationStartDate

GO

How do I get the values I am needing so that I can update the table variable so that I can pull the data in a single query?

Viewing 15 posts - 1 through 14 (of 14 total)

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

ratbak Ten Centuries Points: 1382 More actions · Answer 1

What is "a standard nine value"?

You didn't provide code for GetTheStanine. Does the GetTheStanine procedure return (not select) an integer value that you want in @Energy, @Flex, @Develop, & @Distort? Setting a variable equal to a procedure gets the status ("return") value of the procedure. By default the return value is zero.

If GetTheStanine selects rather than returns the value , you will need to execute those procs into a temp table or table variable, & select the correct column from that. e.g.,

CREATE TABLE #tmpBus
(
COL1 INT,
COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

If GetTheStanine outputs the desired value as an output parameter, you will need to call the stored procedure w/ that output parameter.

If none of the above, then you need to modify GetTheStanine to return the data as a return value, an output parameter, and/or in a select statement and get that value as discussed above.

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 2

Thank you ratbak

Standard Nine (stanine) values are the 9 values in a bell curve as shown in this image

BellCurve

The procedure GetTheStanine returns an integer value or to be specific a tinyint in the range 1-9

the problem I have is SSMS returns the following error

Incorrect syntax near the keyword 'EXEC'.

on each of these 4 lines

SET @Energy = EXEC GetTheStanine @AppID,1,8 
SET @Flex = EXEC GetTheStanine @AppID,1,9 
SET @Develop = EXEC GetTheStanine @AppID,1,12
SET @Distort = EXEC GetTheStanine @AppID,1,18

For the last line, It turns out it just did not see the newer UDF so even though it has the red squiggly line under this dbo.CalculateDependability it is not throwing an error there when I click the execute button to build the procedure.

ratbak Ten Centuries Points: 1382 More actions · Answer 3

Sorry... Didn't notice it before. The syntax to set a variable equal to the return code of a proc is

EXEC @Energy  = GetTheStanine @AppID,1,8; 
EXEC @Flex  = GetTheStanine @AppID,1,9;
EXEC @Develop = GetTheStanine @AppID,1,12;
EXEC @Distort = GetTheStanine @AppID,1,18;

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 4

mjohnson 71479

SSC Eights!

Points: 846

September 9, 2022 at 8:00 pm

#4086016

Excellent !!! Thank you very much!!

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

mjohnson 71479 wrote:

Excellent !!! Thank you very much!!

Any chance of us getting a peek at your "StaNine" stored procedure?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 6

That procedure just calls some individual functions used to get each Stanine.

If you are looking for a formula for determining the bellcurve, we pay for studies to be done which provide us the numbers among the general population, and then it is simply applying the math to see how the score for each section is distributed.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 7

mjohnson 71479 wrote:

That procedure just calls some individual functions used to get each Stanine.
If you are looking for a formula for determining the bellcurve, we pay for studies to be done which provide us the numbers among the general population, and then it is simply applying the math to see how the score for each section is distributed.

My interest is seeing the actual code for the stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 8

hmmm I thought that it was working properly. However, when it returns the results instead of a single list, I am getting all of the individual stanines and then the list so it is like I am running multiple queries. Is there a way to not have this result?

SprocResults

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 9

Jeff, this is the stored procedure

ALTER PROCEDURE [dbo].[GetTheStanine]

@ApplicationID nvarchar(128),
@FamilyID int,
@Dimension int

AS

SET NOCOUNT ON

SELECT 
CASE @Dimension
WHEN 1 THEN dbo.GetMentalAcuityStanin(Acuity,DateViewed,GETDATE()) 
WHEN 2 THEN dbo.GetBusinessTermsStanine(BusMem,DateViewed,GETDATE())  
WHEN 3 THEN dbo.GetMemoryRecallStanine(BusMem,DateViewed,GETDATE()) 
WHEN 4 THEN dbo.GetVocabularyStanine(Vocab,DateViewed,GETDATE())
WHEN 5 THEN dbo.GetPerceptionStanine(NumPercep,PercepData,DateViewed,GETDATE())
WHEN 6 THEN dbo.GetMechanicalInterestStanine(Mech,DateViewed,GETDATE()) 
WHEN 7 THEN dbo.GetMathStanine(Math,MathData,DateViewed,GETDATE()) 
WHEN 8 THEN dbo.GetEnergyStanine(Personality,DateViewed,GETDATE(), @FamilyID)  
WHEN 9 THEN dbo.GetFlexibilityStanine(Personality,DateViewed,GETDATE(), @FamilyID)
WHEN 10 THEN dbo.GetOrganizationStanine(Personality,DateViewed,GETDATE())
WHEN 11 THEN dbo.GetCommunicationStanine(Personality,DateViewed,GETDATE(), @FamilyID)
WHEN 12 THEN dbo.GetDevelopmentStanine(Personality,DateViewed,GETDATE(), @FamilyID)  
WHEN 13 THEN dbo.GetAssertivenessStanine(Personality,DateViewed,GETDATE(), @FamilyID) 
WHEN 14 THEN dbo.GetCompetitivenessStanine(Personality,DateViewed,GETDATE()) 
WHEN 15 THEN dbo.GetToughnessStanine(Personality,DateViewed,GETDATE(), @FamilyID)
WHEN 16 THEN dbo.GetQuestioningStanine(Personality,DateViewed,GETDATE()) 
WHEN 17 THEN dbo.GetMotivationStanine(Personality,DateViewed,GETDATE())
WHEN 18 THEN dbo.GetDistortionStanine(Personality,DateViewed,GETDATE()) 
WHEN 19 THEN dbo.GetEquivocationStanine(Personality,DateViewed,GETDATE())
END
FROM
dbo.AssessmentData
WHERE
ApplicationID = @ApplicationID

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

Ah... I see what you mean. Guess I'll have to look up how to do a "StaNine" and see if I can make a function or a GP proc for it. I'm just interested in doing things like this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ratbak Ten Centuries Points: 1382 More actions · Answer 11

The GetTheStanine procedure you posted is not setting a return value, it is doing a select. How are you not just getting zero from these calls?

EXEC @Energy = GetTheStanine @AppID,1,8; 
EXEC @Flex = GetTheStanine @AppID,1,9;
EXEC @Develop = GetTheStanine @AppID,1,12;
EXEC @Distort = GetTheStanine @AppID,1,18;

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 12

There is a return value in the individual functions being called

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 13

Once again Thank You ratbak I changed the procedure to use a return value and that eliminated all of the other selects being shown

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 14

It looks like you are trying to use a stored procedure as a function - which is generally not a good idea. With that said - there is nothing that would stop you from using a function to call another function.

I would recommend converting that procedure to a function. As a scalar-function it would then be:

SET @value = dbo.fnGetTheStaNine(@AppID, 1, 8)

Basically, all you are doing is creating a wrapper function to the individual functions.

Also, since you are hard-coding the dimension and the FamilyID you could call these directly instead of using a wrapper:

SELECT @Energy  = dbo.GetEnergyStanine(Personality,DateViewed,GETDATE(), 1)  
     , @Flex    = dbo.GetFlexibilityStanine(Personality,DateViewed,GETDATE(), 1)
     , @Develop = dbo.GetDevelopmentStanine(Personality,DateViewed,GETDATE(), 1)  
     , @Distort = dbo.GetDistortionStanine(Personality,DateViewed,GETDATE()) 
  FROM dbo.AssessmentData
 WHERE ApplicationID = @AppID;

I actually think this approach would be better than using a wrapper function/procedure - it makes the code much easier to identify what is being called.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs