CODEWITHSUNDEEP
sqlsql-serverstored-proceduressubquerysql-server-2014
Hasaru Dulan Shamikara
Hasaru Dulan Shamikara

Reputation: 45

How to use stored procedure in a sub query to get value in where clause with using main query data as parameters

I want to use a stored procedure GetSLAClass to get a single value for the where clause as seen in below. And also I want to use WRMaster (main table) values as stored procedure parameters. Is there any solution? I want to do both of this works to get correct data.

This is the error msg

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'EXEC'.

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '.'

Here is my SQL query (currently not working) 

SELECT 
    *
FROM 
    WRMaster W
WHERE 
    DATEDIFF(minute, W.AckedDateTime, W.ResolvedDateTime) <=
        (SELECT Priority
         FROM FMSLAClassPriorityMap
         WHERE SLAClassKey = (EXEC dbo.GetSLAClass W.LocationKey, W.ServicCategoryKey, W.ProblemTypeKey, NULL, ''))
WHERE WRKey=4

Upvotes: 1

Views: 4402

Answers (1)

Hasaru Dulan Shamikara
Hasaru Dulan Shamikara

Reputation: 45

This is a way that can do this,

Declare 
@lk as int,
@sck as int,
@pt as int,
@slaclass as int

select @lk = LocationKey from WRMaster where WRKey=4
select @sck = ServicecategoryKey from WRMaster where WRKey=4
select @pt = ProblemKey from WRMaster where WRKey=4

EXEC   dbo.GetSLAClassOutput @lk,@sck,@pt,NULL,'',  @slaclass output

SELECT *
FROM WRMaster W
WHERE DATEDIFF( minute, W.AckedDateTime, W.ResolvedDateTime )<=
(
  SELECT Priority
  FROM FMSLAClassPriorityMap
  WHERE SLAClassKey=( @slaclass) and PriorityKey = W.PriorityKey
)
where WRKey=4

Upvotes: 1

Related Questions