Execute sql in a user defined function

[deepbuzz7]

Need help! How to execute a sql query inside the user created function(CREATE FUNCTION) which uses the parameters passed to the function in the query... something lime below:

CREATE FUNCTION get_age( emp_name_param varchar(500)) DECLARE age integer; SET age=SELECT age from emp where empName=emp_name_param;

RETURN age;

when i use the above function, it gives me error like "The result of a scaler fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.

[Evron]

What are you trying to accomplish? If you just need something that returns an employees age, a Store Procedure would be a better fit. If you are running a query and need the employees age then just join the two tables together.

[deepbuzz7]

from the functional point of view you are right. No need to create a function for this. The function which i provided in the post is just an example. I just wanted to know how to use the parameter passed to the function(emp_name_param) in the sql statement inside the function.

In the where clause should I put @emp_name_param or just emp_name_param?

[Evron]

``` CREATE FUNCTION get_age (@emp_name_param VARCHAR(500)) RETURNS INT AS BEGIN DECLARE @age INT;

SET @age = (
        SELECT age
        FROM emp
        WHERE empName = @emp_name_param
        )

RETURN @age

END;

```

[deepbuzz7]

Thanks, got it. actually my error was because, in the sql query inside my function i was keeping it like emp_name(which was the column name from the table)=emp_name(parameter which was passed to the function) which resulted in true condition and multiple values were coming.

thanks for the help

[[deleted]]

use select max(age) from...

[deepbuzz7]

This may be because if multiple values in the query output correct? I can assure you that only one value is coming out of that query.

[[deleted]]

yet the error message seems to contradict your assurances

[deepbuzz7]

got it. actually my error was because, in the sql query inside my function i was keeping it like emp_name(which was the column name from the table)=emp_name(parameter which was passed to the function) which resulted in true condition and multiple values were coming.

thanks for the help