ORDER BY line works in QUERY, NOT IN STORED PROCED

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

ORDER BY line works in QUERY, NOT IN STORED PROCED

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-13 : 16:23:32

Hello All,

So I have this

Sql Query:

Select * From Table1
INNER JOIN Table2 ON Table2.Column2 = Table1.Column1
Where Table1.Column4 LIKE '%CH%' AND Table1.Column5 = 30
ORDER BY Table1.Column2

Table1.Column1(primary key) of Table1
Table2.Column1(primary key) of Table2
Table2.Column2(foreign key) of Table1

Function Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[Function1] (@ID int)

RETURNS TABLE
AS
RETURN
(
Select * From Table1
INNER JOIN Table2 ON Table2.Column2 = Table1.Column1
Where Table1.Column4 LIKE '%CH%' AND Table1.Column5 = @ID
ORDER BY Table1.Column2
)

Stored Procedure CODE:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Procedure1]
@ID int

AS
BEGIN
SET NOCOUNT ON;
Select * FROM

dbo.Function1 (@ID)

END

Problem:

When this is RUN as a QUERY - this totally works and is ordered by Column2 of Table1.

HOWEVER When 30 is replaced by a parameter say "@ID" and it is made into a function which is called by a stored procedure, the results are NOT ORDERED BY Table1.Column2.

Why does the ORDER BY Statement not work?

Thanks,
Dasman

==========================
Pain is Weakness Leaving the Body.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-13 : 16:28:19

Why do you have the stored procedure calling the function? Just call the function directly, or write the procedure to do the same query the function does. All you're doing is lowering performance with nested calls like that.

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-13 : 16:52:26

I used a function and the stored procedure for the sake of the parameter @ID to be changeable. However you are right I can just put the query with the parameter into the stored procedure!

All of my dates before this were converted (CONVERT (VARCHAR(8), dbo.Table1.StartTime, 108))

Thus with this order by statement I thought maybe its an issue with the Format the date was in and it works after I converted the both dates to hh:mm:ss!

Thanks!
Dasman

==========================
Pain is Weakness Leaving the Body.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-13 : 16:53:31

Order by is only honoured when it's on the outer query, not a view, subquery or function.

--
Gail Shaw
SQL Server MVP

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-13 : 16:58:56

Thanks a lot Gail. It works when I run ORDER BY statements in a View. Maybe not when I call a view through a stored procedure.

- Dasman

==========================
Pain is Weakness Leaving the Body.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-13 : 17:55:03

It's still not guaranteed to be honoured. The order may come out by accident, but that's it.

CREATE VIEW TablesInOrder AS
SELECT top (100) PERCENT name from sys.objects
ORDER BY name
GO

SELECT * FROM TablesInOrder

That does not return the objects ordered by name. The Order By was completely ignored because it was not in the outer query.

--
Gail Shaw
SQL Server MVP

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-15 : 16:00:43

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

SELECT *
FROM Table1, Table2
WHERE Table2.column2 = Table1.column1
AND Table1.column4 LIKE '%CH%'
AND Table1.column5 = 30
ORDER BY Table1.column2;

This statement has an implicit cursor in T-SQL dialect. It is not really returning a table at all. However, this:

CREATE FUNCTION dbo.Function1 (@in_generic_id INTEGER)
RETURNS TABLE -– tables have no ordering by definition!
AS
RETURN
(SELECT *
FROM Table1, Table2
WHERE Table2.column2 = Table1.column1
AND Table1.column4 LIKE '%CH%'
AND Table1.column5 = @in_generic_id);

has no cursor. Ditto the awful looking Procedure. Ditto a VIEW, derived table or CTE. Oh, ORDER BY is a cursor clause, not a statement. Get the terms and concepts right, and the questions answer themselves.

Good SQL programmers do not use the table-valued functions. They are a bitch to maintain, dialect that will not port and are better done with CTEs, VIEWS and other ANSI/ISO Standard constructs that are declarative and therefore optimizable.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-15 : 16:14:27

Not everything you said above is true. I grow weary of reading your silly tirades. At the very least learn how to format your code.

I wonder how many sales you cost yourself here...

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-18 : 12:09:34

Mr. Celko: I did post the real code of my function, stored procedure, and view. Anyway, thanks for the direction and about the part of saying what SQL version I am using.

Gail and Rob: Thanks for the Help.

Russell: Thanks for the backup.

==========================
Pain is Weakness Leaving the Body.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources