May 20, 2010 at 9:37 am
I'm trying to create a user defined function that will use the people_code_ID as an input parameter and will return the top 1 ethnicity alphabetically. (Students can now declare more than 1 ethnicity, but I need to return only one for this report & they've chosen to do the 1st one alphabetically).
The select statement syntax works fine. I just can't get it wrapped into the UDF.
This is the syntax that works: (I've got just a single people_code_Id listed for testing)
select top 1 [description] from people p
join personethnicity pe
on p.personid=pe.personid
join ipedsfederalcategory ip
on ip.ipedsfederalcategoryid=pe.ipedsfederalcategoryid
where people_code_ID='p000090661'--@people_code_ID
order by description
When I try to put it into a UDF like this, though, it keeps failing:
ALTER FUNCTION [dbo].[TNU_fnTop1Ethnicity] (@people_code_id varchar(10))
RETURNS VARCHAR(255)
AS
/*Returns the top ethnicity alphabetically for people who have declared
more than 1.*/
BEGIN
DECLARE @return varchar(255)
SELECT @return=select top 1 [description] from people p
join personethnicity pe
on p.personid=pe.personid
join ipedsfederalcategory ip
on ip.ipedsfederalcategoryid=pe.ipedsfederalcategoryid
where people_code_ID='p000090661'--@people_code_ID
order by description asc
RETURN @return END
Error msg:
Msg 156, Level 15, State 1, Procedure TNU_fnTop1Ethnicity, Line 10
Incorrect syntax near the keyword 'select'.
Can anyone help, please?
May 20, 2010 at 2:17 pm
this work?
CREATE FUNCTION [dbo].[TNU_fnTop1Ethnicity] (@people_code_id varchar(10))
RETURNS VARCHAR(255)
AS
/*Returns the top ethnicity alphabetically for people who have declared
more than 1.*/
BEGIN
DECLARE @return varchar(255)
select top 1 @return = [description] from people p
join personethnicity pe
on p.personid=pe.personid
join ipedsfederalcategory ip
on ip.ipedsfederalcategoryid=pe.ipedsfederalcategoryid
where people_code_ID='p000090661'--@people_code_ID
order by description asc
RETURN @return END
_____________________________________________________________________
- Nate
May 20, 2010 at 3:23 pm
Yes, that worked brilliantly! Thank you so much! 😀
January 5, 2016 at 2:43 pm
The syntax error was because you have a select sub-query
Select @Return = Select ....
Your second Select needs to be in parens
Select @Return = (Select ....... )
January 5, 2016 at 2:50 pm
Ray Herring (1/5/2016)
The syntax error was because you have a select sub-querySelect @Return = Select ....
Your second Select needs to be in parens
Select @Return = (Select ....... )
This thread is 5 years old. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 6, 2016 at 5:13 am
Sean Lange (1/5/2016)
This thread is 5 years old. 😉
What's your point? The Original post still pops up in searches and is still valid.
I realize the first reply "solved" the problem and is in fact the coding style I use. I was simply identifying the actual syntax error that was the root of the problem.
January 6, 2016 at 5:49 am
Ray Herring (1/6/2016)
Sean Lange (1/5/2016)
This thread is 5 years old. 😉
What's your point? The Original post still pops up in searches and is still valid.
I realize the first reply "solved" the problem and is in fact the coding style I use. I was simply identifying the actual syntax error that was the root of the problem.
Then why not take the next step and make it more performant by converting it to an ITVF?
January 6, 2016 at 6:59 am
The OP question was not about performance. It was about syntax
January 6, 2016 at 7:27 am
Ray Herring (1/6/2016)
The OP question was not about performance. It was about syntax
We try to go beyond just answering the OP's question around here. We try to share our knowledge and help people get better at writing sql. Almost always this means helping the OP find a better way to solve their problem then they are currently trying to do. And replacing a scalar function with iTVF is certainly a major improvement. Want to take a shot at this one? If not, I will be happy to do it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 6, 2016 at 8:07 am
I am sorry that I fall short of your expectations and apologize for not meeting your standards.
I will of course, bow to your superior knowledge and refrain from future contributions.
January 6, 2016 at 8:24 am
Ray Herring (1/6/2016)
I am sorry that I fall short of your expectations and apologize for not meeting your standards.I will of course, bow to your superior knowledge and refrain from future contributions.
Gosh, I was not intending to be arrogant or condescending in any way shape or form and I apologize that is how my comment was received. I was trying to help you understand that SSC is a bit different than most online forums. We actually try to help people here instead of just "here is the answer". Then I was intentionally NOT going to convert this to an iTVF to allow you a chance to post it if you wanted. By all means I certainly don't want you to leave. On the contrary I would be thrilled if you would stick around and offer your perspective on this and many other topics as you obviously have a solid understanding of the language. Let's chalk this up to a misunderstanding as misinterpretation over the internet.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 6, 2016 at 8:35 am
Spoiler alert, I think this works as the itvf. No tables or data to test against. Only issue I have is not being able to identify which table the column description is coming from.
CREATE FUNCTION [dbo].[TNU_fnTop1Ethnicity] (@people_code_id varchar(10))
RETURNS TABLE
AS RETURN
/*Returns the top ethnicity alphabetically for people who have declared more than 1.*/
select top 1
[description]
from
dbo.people p
inner join dbo.personethnicity pe
on p.personid = pe.personid
inner join dbo.ipedsfederalcategory ip
on ip.ipedsfederalcategoryid = pe.ipedsfederalcategoryid
where
people_code_ID = @people_code_ID
order by
[description] asc;
go
January 6, 2016 at 10:31 am
Ray Herring (1/6/2016)
I am sorry that I fall short of your expectations and apologize for not meeting your standards.I will of course, bow to your superior knowledge and refrain from future contributions.
You can take offense if you want to, but that's not at all how I meant it. I appreciate anyone who contributes good stuff to the converations.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply