SQLTeam.com | Weblogs | Forums

Call stored procedure from in Operator


#1

Not sure what I am doing wrong
SELECT TOP (1) a.DateEntered, e.LastName + ', ' + e.FirstName + ' (' + CAST(e.Id AS varchar(10)) + ')' AS empname, a.ActionId, a.StatusId
FROM dbo.tblTWRecordAction AS a INNER JOIN
dbo.tblEmployee AS e ON a.EmployeeId = e.Id
WHERE (a.EmployeeId IN (exec getTWAppInfo 43641) AND (a.StatusId = 3))


#2

this:

IN (exec getTWAppInfo 43641)

Is not valid SQL. You can put a list of values or a subquery there but not a call to a stored procedure. See:

IN (Transact-SQL)


#3

Well. That sucks?


#4

Why not explain what you are trying to do here. There's bound to be another way to do it.


#5

CREATE TABLE #getTWAppInfo ( EmployeeID int )

INSERT INTO #getTWAppInfo
EXEC dbo.getTWAppInfo '43641'

SELECT ...
FROM ...
WHERE (a.EmployeeID IN (SELECT EmployeeID FROM #getTWAppInfo)) AND (a.StatusId = 3)