error with sql function creation

Question

Getting the following error when trying to create this sql function in SQL2k5. Any ideas here? It runs fine outside the function.

UPDATE THIS work NOW ,but I have to come to the realization that this needs to be in the form of a view since I need to do an inner join on the product_id so this current form will only work when I am passing the product ID. Any thoughts?

Msg 102, Level 15, State 1, Procedure getoptionlist, Line 13 Incorrect syntax near ')'.

CREATE FUNCTION dbo.getoptionlist 
(@ProductID as int)
RETURNs varchar(101) 
AS
BEGIN
declare @Return varchar(101)
 SELECT SUBSTRING(
(SELECT ','  + s.Name + '~0'
FROM vOptions_details s
where product_id=@ProductID
ORDER BY s.Name
FOR XML PATH('')),2,200000) 
)
end
return @return

Answer 1

A few problems:
- one too many parentheses
- return statement should be before "end"
- you need to set the @return variable

CREATE FUNCTION dbo.getoptionlist 
(@ProductID as int)
RETURNs varchar(101) 
AS
BEGIN
declare @Return varchar(101)
 SELECT @return = SUBSTRING(
(SELECT ','  + s.Name + '~0'
FROM vOptions_details s
where product_id=@ProductID
ORDER BY s.Name
FOR XML PATH('')),2,200000) 

return @return
end

Answer 2

You have one too many end brackes and the RETURN statement needs to be inside the BEGIN..END block.

Change the last 3 lines from

)
end 
return @return

to:

return @return
end