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

this is why indenting properly and formatting your code can be helpful — KM.
– KM., Commented Feb 10, 2010 at 18:43

user121301 · Accepted Answer · 2010-02-10 18:40:34Z

6

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

answered Feb 10, 2010 at 18:40

user121301

Sign up to request clarification or add additional context in comments.

4 Comments

OMG Ponies Over a year ago

+1: The RETURN statement needs to be within the BEGIN/END block.

Mike Burton Over a year ago

+1: There should be no bare SELECT within a function on SQL Server, and the return value needs to be set.

jeff Over a year ago

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?

user121301 Over a year ago

@jeff: I think that changes the question enough that you might want to ask it separately. If not, at least you need to edit the current question with a short description of the tables/columns involved and the sample data and results you would want. Thanks.

AdaTheDev · Accepted Answer · 2010-02-10 18:41:20Z

0

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

answered Feb 10, 2010 at 18:41

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

1 Comment

jeff Over a year ago

Got this working, 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?

Collectives™ on Stack Overflow

error with sql function creation

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related