Please help ! - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Please help !

Author

Topic

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2002-12-11 : 03:32:17

Hi All,

I am using a sql function to retrive records. But when I use "order by" clause then it doesn't work. It doesn't give any error but shows the records without any change. Please help me. Thanx in advance.

Sachin
sachin.samuel@fqsltd.com

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-12-11 : 03:41:15

Hello!

I think you'll need to show us some code, so that we can understand what you're trying to do. Also, create tables statements and some inserts if you want to give us the whole picture.

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2002-12-11 : 04:48:00

Here is my code.

--Creating SQL function
CREATE function test_Function()
Returns @temp_tab table
(
booking_ref varchar(40)
)
as
begin
Insert into @temp_tab
Select booking_ref from booking_master

return
end

--Query to retrive data through the funtion.
Select * from test_Function() order by booking_ref

When I use,
"Select * from test_Function()"

Data is displayed, but when I say,

"Select * from tst_test() order by booking_ref"

Data is not displayed in order of "booking_ref"

Please help me

Thanx
Sachin

mr_mist
Grunnio

1870 Posts

Posted - 2002-12-11 : 05:00:53

quote:

When I use,
"Select * from test_Function()"

Data is displayed, but when I say,

"Select * from tst_test() order by booking_ref"
Data is not displayed in order of "booking_ref"

These are two different functions though? Does tst_test work from the same information?

-------
Moo.

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2002-12-11 : 05:18:15

I am very sorry I just miss-spelled the function name

Here is my latest Code.

--Creating SQL function
CREATE function test_Function()
Returns @temp_tab table
(
booking_ref varchar(40)
)
as
begin
Insert into @temp_tab
Select booking_ref from booking_master

return
end

--Query to retrive data through the funtion.
Select * from test_Function() order by booking_ref

When I use,
"Select * from test_Function()"

Data is displayed, but when I say,

"Select * from test_Function() order by booking_ref"

Data is not displayed in order of "booking_ref"

Please help me

Thanx
Sachin

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-12 : 23:49:21

USE NORTHWIND
GO
CREATE function test_Function()
Returns @temp_tab table
(
CategoryName varchar(40)
)
as
begin
Insert into @temp_tab
Select CategoryName from Categories

return
end
GO
Select * from test_Function()
Select * from test_Function() order by CategoryName

Interesting this seems to work fine. Can you post some some sample data from booking_master.

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-12-13 : 04:47:54

Sachin's code is missing the "GO" statement before the final select statement. The "GO" is crucial because "ORDER BY" is not allowed in a table returning UDF.

By the way, why are you declaring a temp table instead of just selecting columns?

XMPL:
CREATE function test_Function()
Returns TABLE
as
RETURN
(
Select booking_ref from booking_master
)
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources