Stuck Creating a Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stuck Creating a Function

Author

Topic

schizmark
Starting Member

5 Posts

Posted - 2008-02-17 : 15:39:00

Alright, I'll write this again. The first try didn't go so well. I am learning SQL Server from an online tutorial. Maybe there are a lot who do that, maybe not, but it's a good tutorial. There are projects to work on at the end of each section, and they involve more than just regurgitating everything I just read. They even make me figure out stuff that wasn't covered in the section (which is probably a mistake, but it makes the learning more interesting and I like it). The problem is that I've hit one of those places and I can't figure it out. The last project for this section is to write a function that calculates and displays the diameter of the base, the circumference of the base, the base area, the side area, the total area, and the volume, given the radius and the height of a cylinder. Now I had to do the same thing as a query for the previous section, and hunting down all the formulae involved was more time-consuming than writing the thing, but I'm stumped here. Every example and work-along project was, for lack of a better description, "one function, one result". So how do I get a function to return multiple results? If I've posted this in the wrong place, someone please let me know where I might find the information I need. For those of you who may see this and be stunned that I haven't figured out the obvious, remember you were new once too. Thanks in advance.

Now that I've seen some of the other posts, maybe I should clarify. Here's what I had from the last project.

declare @Radius float(10),
@BaseDiameter float(10),
@BaseCircumference float(10),
@BaseArea float(10),
@Height float(10),
@SurfaceArea float(10),
@TotalArea float(10),
@Volume float(10)
set @Radius = 19.1
set @BaseDiameter = @Radius * 2
set @BaseCircumference = @BaseDiameter * PI()
set @BaseArea = power(@Radius,2) * PI()
set @Height = 16.27
set @SurfaceArea = 2 * (pi() * @Radius * @Height)
set @TotalArea = 2 * (pi() * @Radius * @Height) + 2 * (power(@Radius,2) * PI())
set @Volume = (power(@Radius,2) * pi()) * @Height
select @Radius as [Base Radius],
@BaseDiameter as [Base Diameter],
@BaseArea as [Base Area],
@Height as Height,
@SurfaceArea as [Side Area],
@TotalArea as [Total Area],
@Volume as Volume
go

So how do I make a function that does the same thing?

Schiz

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-17 : 18:35:23

You will need to use a table-valued function in order to return multiple values. Check out CREATE FUNCTION in SQL Server Books Online for examples.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

schizmark
Starting Member

5 Posts

Posted - 2008-02-17 : 20:25:50

Thanks much. I had been going over those, but didn't know there was a specific type of function I should look for. This whole section involved scalar-valued functions, so I guess they got ahead of themselves.

If you haven't tried, you've already failed.
Schiz

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 10:08:55

The BoL covers this. Here is a good link:
http://msdn2.microsoft.com/en-us/library/aa258261(SQL.80).aspx

Look at the Examples section under: C. Multi-statement table-valued function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources