Sorting by Variable in Stored Procedure

Sorting by Variable in Stored Procedure

Post by Packer Fa » Fri, 12 Apr 2002 04:32:09



Hi All,

I'm trying to pass in a parameter to a stored procedure to ORDER BY, but am
having problems.

This is what I'm doing...

CREATE PROCEDURE dbo.DAVID GetSearchList

AS
BEGIN
    SELECT Street
    FROM FSBO_Listing_Fields
    WHERE Display=1
    AND User_Display=1

END
GO

Checking the syntax gives me this error:
Error 1008: The SELECT item identitied by the ORDER BY number 1 contains a
variable as part of the expression identifying a column position. Variables
are only allowed when ordering by an expression referencing a column name.



Thanks,

Dave

 
 
 

Sorting by Variable in Stored Procedure

Post by Alexander Chigri » Fri, 12 Apr 2002 03:18:44


You should use dynamic SQL, in this case.
This is the example:

CREATE PROCEDURE dbo.DAVIDGetSearchList

AS
BEGIN




END
GO

With best regards,
Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).



> Hi All,

> I'm trying to pass in a parameter to a stored procedure to ORDER BY, but
am
> having problems.

> This is what I'm doing...

> CREATE PROCEDURE dbo.DAVID GetSearchList

> AS
> BEGIN
>     SELECT Street
>     FROM FSBO_Listing_Fields
>     WHERE Display=1
>     AND User_Display=1

> END
> GO

> Checking the syntax gives me this error:
> Error 1008: The SELECT item identitied by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position.
Variables
> are only allowed when ordering by an expression referencing a column name.



> Thanks,

> Dave


 
 
 

Sorting by Variable in Stored Procedure

Post by BP Margoli » Fri, 12 Apr 2002 07:47:15


Dave,

Check out the "orderbyvariable" entry in the SQL Server FAQ at
www.sqlserverfaq.com

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Hi All,

> I'm trying to pass in a parameter to a stored procedure to ORDER BY, but
am
> having problems.

> This is what I'm doing...

> CREATE PROCEDURE dbo.DAVID GetSearchList

> AS
> BEGIN
>     SELECT Street
>     FROM FSBO_Listing_Fields
>     WHERE Display=1
>     AND User_Display=1

> END
> GO

> Checking the syntax gives me this error:
> Error 1008: The SELECT item identitied by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position.
Variables
> are only allowed when ordering by an expression referencing a column name.



> Thanks,

> Dave

 
 
 

1. How to: Get an output variable from a stored procedure in a variable on my ASPpage

Hello, i would like to execute a stored procedure from my ASP-page. When the
procedure fails it should return a friendly
error-code and a description in the browser (not the standard
ODBC-errorstuff). When it succeeds it should return the
result of the executed stored procedure. I know this has something to do
with OUTPUT declaration in my stored procedure definition, but I don't have
a clue
how to get this output-variable in a variable on my ASP-page so that I can
do checks on it and write nice messages to the screen.
Maybe you can help me.
Thanks in advance.

Regards,

Donald Lubbers
-------------------------------------------------------
INTERNOVATION
E-commerce en Webdevelopment

Website: www.internovation.nl
-------------------------------------------------------

2. Microsoft Search Service

3. storing a value of a count in a variable within a stored procedure

4. DNS lookup inside of FMP?

5. HOWTO: store a table field into a local variable in a stored procedure

6. DBAs and stored procedure programmers needed in Florida.

7. Stored Procedure - Storing a Variable

8. Index Question

9. Executing a sql statement stored as a variable in a stored Procedure

10. Storing results of stored procedure in variables

11. Calling a Java Stored Procedure from another Java Stored Stored Procedure

12. Sorting Stored procedure using a parameter

13. stored procedure for paging, with sorting