quote:

---

Adam writes "I have read the FAQ's and many posts, but from what I can gather, you cannot use the EXEC statement in a User Defined Function. I need to return a table of results in a UDF obtained from passing an 'IN' clause. I may have missed something, but I get errors when using:


CREATE FUNCTION dbo.fnScheduleCountItems (@RecordType as smallint, @JobIDs as nvarchar(1900))
RETURNS @TheTable TABLE (
quantity float,
recordtype int,
recordID int
)
AS

BEGIN

Declare @SQL VarChar(2000)

select @sql = ''

if @RecordType = 17
select @SQL= 'insert into @TheTable select count(ID) as Quantity, 17, CID from JobsC where ID in (' + @JobIDs + ') group by CID'

else if @RecordType = 19
select @SQL = 'insert into @TheTable select count(ID) as Quantity, 19, AID from JobsA where ID in ( ' + @JobIDs + ' ) group by AID'

else if @RecordType = 5
select @SQL = 'insert into @TheTable select count(ID) as Quantity, 5, PID from JobsP where ID in ( ' + @JobIDs + ' ) group by PID'

if @SQL <> ''
exec @SQL

RETURN

END


When you select from it it says that it cant find the stored procedure.

Any thoughts, as I'm banging my head on this.

Thanks

Adam"

---

quote:

---

When you select from it it says that it cant find the stored procedure

---

I hope Adam is not trying to run something like
EXEC fnScheduleCountItems


OS

quote:

---

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the Microsoft® SQL Server™ query optimizer is likely to reuse the execution plan it generates for the first execution.

---