Hello,

I have the following function and it won't let me pass the syntax.  Help
would be appreciated.

CREATE FUNCTION dbo.udf_dispMandReq (@empnum varchar(11)) 
RETURNS table
AS 
BEGIN
    declare @divnum int
    SELECT @divnum = DpsDiv_Num FROM vDPSMain WHERE CAST(DpsIndv_StateNum
AS varchar(11)) = @empnum

    RETURN (SELECT TOP 100 PERCENT Pc.PCourse_Name, Pmc.PMand_NumClassReq,
Peex.PExemp_NumClassReq, X.NumClass
        FROM dbo.TblPOSTMandCourse Pmc INNER JOIN
            dbo.TblPOSTCourse Pc ON Pmc.PCourse_ID = Pc.PCourse_ID AND
Pmc.DpsDiv_Num = @divnum LEFT OUTER JOIN
            dbo.TblPOSTEmpExemp Peex ON Pmc.PMand_ID = Peex.PMand_ID AND
Peex.Emp_Num = @empnum LEFT OUTER JOIN
            (SELECT Pmc2.PMand_ID, COUNT(Pec.PCourse_ID) AS NumClass
            FROM dbo.TblPOSTEmpCourse Pec INNER JOIN
                dbo.TblPOSTMandCourse Pmc2 ON Pec.PCourse_ID = Pmc2.PCourse_ID AND
Pec.Emp_Num = @empnum
                AND Pmc2.DpsDiv_Num = @divnum
            GROUP BY Pmc2.PMand_ID) AS X ON Pmc.PMand_ID = X.PMand_ID
        ORDER BY Pc.PCourse_Name)
END

Mia J.

*** Sent via Developersdex http://www.developersdex.com ***

What is the error message?

I think I noticed that you are missing a ) at the end.


CREATE FUNCTION dbo.udf_dispMandReq (@empnum varchar(11))
RETURNS table
AS
BEGIN
        declare @divnum int
        SELECT @divnum = DpsDiv_Num FROM vDPSMain WHERE
CAST(DpsIndv_StateNum AS varchar(11)) = @empnum

        RETURN (
            SELECT TOP 100 PERCENT
                Pc.PCourse_Name, Pmc.PMand_NumClassReq,Peex.PExemp_NumClassReq,
X.NumClass
            FROM
                dbo.TblPOSTMandCourse Pmc
                INNER JOIN dbo.TblPOSTCourse Pc ON Pmc.PCourse_ID = Pc.PCourse_ID
AND Pmc.DpsDiv_Num = @divnum
                LEFT OUTER JOIN dbo.TblPOSTEmpExemp Peex ON Pmc.PMand_ID =
Peex.PMand_ID AND Peex.Emp_Num = @empnum
                LEFT OUTER JOIN (SELECT
                        Pmc2.PMand_ID,
                        COUNT(Pec.PCourse_ID) AS NumClass
                    FROM
                        dbo.TblPOSTEmpCourse Pec
                        INNER JOIN dbo.TblPOSTMandCourse Pmc2 ON Pec.PCourse_ID =
Pmc2.PCourse_ID AND Pec.Emp_Num = @empnum AND Pmc2.DpsDiv_Num = @divnum
                    GROUP BY
                        Pmc2.PMand_ID
                    ) AS X ON Pmc.PMand_ID = X.PMand_ID
                )

Mij (m***@infi.net) writes:
Your function is a mix of a multi-statement function and a inline-function.
An inline function has a SELECT statement as its sole body. You cannot
use variables in it.

If you use variables, you need to write a multi-statement function,
in which case you must declare a return table that you insert your
data into. For an example of a multi-statement function, see
http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings.

But inline functions are preferrable. They are not really functions at
all, but macros that are expanded into the query, so that the optimizer
can recast computation order to get a more effecient plan. On the other
hand, return tables from multi-statement functions are completely opaque
to the optimizer, and it has to make guesses about it.

Finally, I notice that your function includs an ORDER BY statement.
This is completely pointless. There is only one way to be guaranteed
to get data back in a certain order from SQL Server, and that is an
ORDER BY in the SELECT statement returns the data. That is,

   SELECT ... FROM yourfunction()

could return data in any order. In SQL 2000, it typically doesn't,
but in SQL 2005 it does.

Thank you for the tips.  I will check out the link you gave.

Mia J.

*** Sent via Developersdex http://www.developersdex.com ***

Help with constraint
How does engine decide whether to scan or seek index
Nightmare Query
Table design (search with ranking)
Problem with DateAdd & GetDate()