January 2, 2007 at 8:05 am
I can make this to work
DECLARE @PositionID int
SELECT @PositionID = PositionID FROM Employee WHERE ID = 5
SELECT dbo.fnFindPositionSupervisor(@PositionID)
but this returns a incorrect syntax error
SELECT dbo.fnFindPositionSupervisor(SELECT PositionID FROM Employee WHERE ID = 5)
Your thoughts ?
January 2, 2007 at 8:14 am
Assuming that the function returns a scalar value :
SELECT dbo.fnFindPositionSupervisor (PositionID) FROM Employee WHERE ID = 5
BTW it is considered a bad (if not worst) pratice to call a function from a select that does a select to the same or any other tables. This cause the function to act as a cursor which is really bad for performance.
January 2, 2007 at 8:18 am
The function is looking for an input field as data type INT.
When you did this
SELECT dbo.fnFindPositionSupervisor(SELECT PositionID FROM Employee WHERE ID = 5)
The function did not know the data type of (SELECT PostionID FROM Employee WHERE ID = 5).
My 2 cents
In BOL about parameter of function
@parameter_name
Is a parameter in the user-defined function. One or more parameters can be declared in a CREATE FUNCTION statement. A function can have a maximum of 1,024 parameters. The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined. When a parameter of the function has a default value, the keyword "default" must be specified when calling the function in order to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value.
Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the function; the same parameter names can be used in other functions. Parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects.
January 2, 2007 at 9:42 am
Thanks for your replies.
Remi, why is it a cursor if a function from a select that does a select from the same/different table? Is that like a normal Inner Join ?
The requirement is to find the Supervisor for a given employee. The table definitions
Employee Table
==============
ID int
PositionID int
Status tinyint -- (1-Active, 2-Applicant, 3-Quit)
...
EmployeePosition Table
======================
ID int,
ParentPositionID int
...
The function is
CREATE FUNCTION fnFindPositionSupervisor(@PositionID smallint)
RETURNS smallint AS
BEGIN
DECLARE @ParentPosID smallint
DECLARE @SupervisorEmployeeID smallint
SET @SupervisorEmployeeID = 0
SELECT @ParentPosID = ISNULL(ParentPositionID,0) FROM EmployeePositions WHERE (ID = @PositionID)
WHILE @ParentPosID <> 0
BEGIN
IF (SELECT COUNT(*) FROM Employee WHERE (PositionID = @ParentPosID) AND (Status = 1)) > 0
BEGIN
SELECT @SupervisorEmployeeID = ID FROM Employee WHERE (PositionID = @ParentPosID) AND (Status = 1)
SET @ParentPosID = 0
END
ELSE
SELECT @ParentPosID = ISNULL(ParentPositionID,0) FROM EmployeePositions WHERE (ID = @ParentPosID)
END
RETURN @SupervisorEmployeeID
END
So if there no employee at a position, it goes up to the ParentPosition.
January 3, 2007 at 3:21 am
UDF needs one value as a parameter. (SELECT PositionID FROM Employee WHERE ID = 5) can give a resultset that has several rows. However, this should cause "Subquery returned more than 1 value" error, not incorrect syntax... and if the problem is what Loner wrote, it should say something like "Syntax error converting the varchar value 'xxxxx' to a column of data type int." Could you post precisely what is the error message?
If you use column name as a parameter of a function (as Remi posted), the function is executed for each value of the column - because there is always one value of parameter for each row, no problems arise... except that row-by-row calculations slow down processing.
January 3, 2007 at 6:20 am
SELECT PositionID FROM Employee WHERE ID = 5 returns only one row. ID is the primary key for Employee table. The error I get when I execute the query
SELECT dbo.fnFindPositionSupervisor(SELECT PositionID FROM Employee WHERE ID = 5)
is
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Since in the inner query returns just one row, the function will be executed just one.
January 3, 2007 at 6:50 am
Notwithstanding the above try
SELECT dbo.fnFindPositionSupervisor((SELECT PositionID FROM Employee WHERE ID = 5))
Far away is close at hand in the images of elsewhere.
Anon.
January 3, 2007 at 7:15 am
Thanks David. That worked.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply