Arun D Arun D - 2 months ago 13
SQL Question

How to use a function in Select Query

I need to use a function in a select query. Below is my code for Function and Select Query.

Sample Function:

CREATE FUNCTION GetASRPlanCurrentStatus(@ChvnPlanNo nvarchar(50)) RETURNS NVARCHAR(50)

AS
BEGIN

Declare
@ChvnStatus nvarchar(50) = NULL,
@PlanStatus nvarchar(50) = 'Not yet initiated for process'


SET @ChvnStatus =
CASE
WHEN (SELECT
COUNT(*)
FROM Evaluation
WHERE PlanNo = @chvnPlanNo
AND ISNULL(IsFreezed, 0) = 1
AND ISNULL(SendForRecruiter, 0) = 1
AND ISNULL(IsPEScoreUploaded, 0) = 1
AND ISNULL(SentForTeamLeaderApproval, 0) = 1)
= (SELECT
COUNT(*)
FROM Evaluation
WHERE PlanNo = @ChvnPlanNo) THEN 'Approved'
END
SET @PlanStatus = ISNULL(@ChvnStatus, @PlanStatus)
RETURN @PlanStatus

END


My Select Query:

SELECT
'0' AS ResultTag,
GetASRPlanCurrentStatus('ASR2017 - 3') AS Overallstatus,
6' AS ProcessOrder


The above query is not working. I don't know what is wrong. anyone guide me to solve this. Thanks in advance

Require Output:

---------- ---------- ----------
Result Tag Overallstatus ProcessOrder
---------- ---------- ----------
0 Approved 6

Answer

Please try putting the scheme in front of your function

SELECT 
 '0' AS ResultTag,
  dbo.GetASRPlanCurrentStatus('ASR2017 - 3') AS Overallstatus,
  6' AS ProcessOrder
...