CODEWITHSUNDEEP
sqlsql-server-2008
Arun D
Arun D

Reputation: 464

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

Upvotes: 0

Views: 17526

Answers (2)

Mansoor
Mansoor

Reputation: 4192

Use below query to call function in SELECT clause :

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

Upvotes: 0

Kevin
Kevin

Reputation: 781

Please try putting the scheme in front of your function

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

Upvotes: 1

Related Questions