> Guys,
> This is simple & stupid question. Please bear with me.
> I am trying to write a stored procedure with one parameter. Based on
> this parameter, the records will be ordered. THis is the SQL for my
> SP:
> CREATE PROCEDURE sp_viewFacultyPersonal
> AS
> select * from vw_FacultyPersonal
> When I run this query, it flags me this error:
> -----------------------
> Server: Msg 1008, Level 15, State 1, Procedure sp_viewFacultyPersonal,
> Line 3
> The SELECT item identified 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.
> ------------------------
You are missing the basic idea of a parameter; this is freshman
programming and not SQL. And SQL is a lousy first language to learn.
A parameter is a value passed into the procedure; it is not a column
name!
I hope that you do know the difference between a field and column; you
made it 200 charactres long -- you really have a column name that
long??? Or do you just want to increase the likelihood of errors when
people type in a value?
Never use a SELECT * in production code. Read the ISO-11179 Standard
conventions for naming data elements; then take tht silly "vw-"prefix
off the table name in your FROM clause.
Never put a "sp_" on a stored procedure, unless you really are
declaring a system procedure and want to have it handled differently
than an application procedure.
Standard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it
can only see the column names that appear in the SELECT clause list
that was used to build the result set. BP will now chime in that
SQL-99 (officially called "a standard in progress" and not recognized
by the U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per
column:
SELECT
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
...
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
THEN CAST (a AS CHAR(n))
THEN CAST (b AS CHAR(n))
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
THEN CAST (a AS CHAR(n))
THEN CAST (b AS CHAR(n))
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
.. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting
option so you do not have worry about CAST() operations.
SELECT ...
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;
by