SQL Server - Error Msg 102 while creating function in sql server

Asked By Sandy on 14-Dec-10 07:10 AM
I am trying to create a function that takes an input parameter and executes a query based on that.
The function needs to return the entire resultset to the calling environment in the form of an array.
But iam getting an error "Msg 102, Level 15, State 1, Procedure sample_test, Line 25
Incorrect syntax near '@v_input'."

CREATE FUNCTION sample_test (@v_input  varchar(max))
RETURNS @returnArray TABLE
(employeeid int,
employeename varchar,
empstatus varchar)
AS
begin
SELECT * FROM employees WHERE employeeid IN @v_input AND empstatus = 'A'
return
go
Nowshad M replied to Sandy on 14-Dec-10 07:11 AM
Hi,
Here it is

CREATE FUNCTION sample_test (@v_input  varchar(max))
RETURNS @returnArray TABLE 
(employeeid int,
employeename varchar,
empstatus varchar)
AS
begin
SELECT * FROM employees WHERE employeeid IN(@v_input) AND empstatus = 'A'
return
go

Sandy replied to Nowshad M on 14-Dec-10 10:29 AM
This didn't work.

CREATE FUNCTION sample_test (@v_input  varchar(max))
RETURNS @returnArray TABLE
(employeeid int,
employeename varchar,
empstatus varchar)
AS
begin
SELECT * FROM employees WHERE employeeid IN (@v_input) AND empstatus = 'A'
return
go

I am getting an error
"Msg 102, Level 15, State 1, Procedure sample_test, Line 9
Incorrect syntax near 'return'."
Pankaj replied to Sandy on 15-Dec-10 04:09 AM
Try this

CREATE FUNCTION sample_test (@v_input varchar(max))

RETURNS @returnArray TABLE

(employeeid int,

employeename varchar,

empstatus varchar)

AS

begin

insert into @returnArray SELECT * FROM employees WHERE employeeid IN (@v_input) AND empstatus = 'A'

return

end

go