A simple question on Stored procedure with parameter

A simple question on Stored procedure with parameter

Post by Giri » Thu, 24 Oct 2002 23:48:32



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:


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.
------------------------
Could somebody please tell me how I can achieve this

Thanks

Girish

 
 
 

A simple question on Stored procedure with parameter

Post by Sameer Mahaja » Fri, 25 Oct 2002 04:48:40


u can use the usual




trick.


> 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.
> ------------------------
> Could somebody please tell me how I can achieve this

> Thanks

> Girish


 
 
 

A simple question on Stored procedure with parameter

Post by Erland Sommarsko » Fri, 25 Oct 2002 06:56:10


[posted and mailed, please reply in news]


> 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


You can do:


               WHEN 'this_col' THEN this_col
               WHEN 'that_col' THEN that_col
               ...
            END DESC

Note that this_col, that_col etc must all be of the same datatype. If you
can order both by, say, integer and character columns you can say:


               WHEN 'this_charcol' THEN this_charcol
               WHEN 'that_charcol' THEN that_charcol
               ...
               ELSE NULL
            END DESC,

               WHEN 'this_numcol' THEN this_numcol
               WHEN 'that_numcol' THEN that_numcol
               ELSE NULL
            END

Note also that ASC/DESC must come after the CASE expression.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

 
 
 

A simple question on Stored procedure with parameter

Post by Bas » Fri, 25 Oct 2002 07:18:15


I still think it's a freaking shame you can do:

    CREATE PROCEDURE sp_viewFacultyPersonal
    AS
    select * from  vw_FacultyPersonal
    order by 1 desc  --sort on column no. 1

but you *can't*:

    CREATE PROCEDURE sp_viewFacultyPersonal

    AS
    select * from  vw_FacultyPersonal

Though I feel that user decided sorting should be handled by the application
(let's say in a grid) and not by the database. After all, the app contains
the presentation layer...

Cheers,

Bas


> [posted and mailed, please reply in news]


> > 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

> You can do:


>                WHEN 'this_col' THEN this_col
>                WHEN 'that_col' THEN that_col
>                ...
>             END DESC

> Note that this_col, that_col etc must all be of the same datatype. If you
> can order both by, say, integer and character columns you can say:


>                WHEN 'this_charcol' THEN this_charcol
>                WHEN 'that_charcol' THEN that_charcol
>                ...
>                ELSE NULL
>             END DESC,

>                WHEN 'this_numcol' THEN this_numcol
>                WHEN 'that_numcol' THEN that_numcol
>                ELSE NULL
>             END

> Note also that ASC/DESC must come after the CASE expression.

> --

> I support PASS - the definitive global community for SQL Server
> professionals - http://www.sqlpass.org
> The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

 
 
 

A simple question on Stored procedure with parameter

Post by Steve Kas » Fri, 25 Oct 2002 07:58:03


If you do it this way, it's not so bad - it's safe when the parameter
is an integer, but there are risks if you do a similar thing with a
user-supplied string parameter.

CREATE PROCEDURE sp_viewFacultyPersonal

      AS


      select * from  vw_FacultyPersonal

      '


Steve Kass
Drew University


> u can use the usual




> trick.



> > 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.
> > ------------------------
> > Could somebody please tell me how I can achieve this

> > Thanks

> > Girish

 
 
 

A simple question on Stored procedure with parameter

Post by --CELKO » Fri, 25 Oct 2002 08:59:12



> 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;

 
 
 

A simple question on Stored procedure with parameter

Post by Tony Rogerso » Fri, 25 Oct 2002 15:58:04


Just to add - this method of putting additional columns on the select
statement may well bloat your result set resulting in poor performance - so
watch scalability.

If the size of data is important and needs to be kept to a minimum use ORDER
BY with CASE as shown by Erland.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]



> > 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;

 
 
 

A simple question on Stored procedure with parameter

Post by Erland Sommarsko » Fri, 25 Oct 2002 16:40:22


Bas (nomailplease) writes:
> I still think it's a freaking shame you can do:

>     CREATE PROCEDURE sp_viewFacultyPersonal
>     AS
>     select * from  vw_FacultyPersonal
>     order by 1 desc  --sort on column no. 1

> but you *can't*:

>     CREATE PROCEDURE sp_viewFacultyPersonal

>     AS
>     select * from  vw_FacultyPersonal


If you feel that this is something that should be added to SQL Server,

However, personally I would not like to see such a feature. If the view
or table is changed, the number 5 might suddenly give you a new ordering,
which is confusing and least of all robust code.

You should also keep in mind that when SQL Server builds the plan for a
query it also takes the ORDER BY in regard. With a syntax like the above,
SQL Server would have to recompile the procedure on each invocation, which
may or may not have a significant impact on the performance of the system.
But it would not be a behaviour that users might anticipate.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

A simple question on Stored procedure with parameter

Post by Giri » Sat, 26 Oct 2002 04:56:25


Hey guys,

I really appreciate your time and effort. I tried with case statement and it works.

Thank you

Girish

 
 
 

A simple question on Stored procedure with parameter

Post by Erland Sommarsko » Sat, 26 Oct 2002 06:32:26



> 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.

Now! Look! Joe is commenting on something which is SQL Server specific!

> 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,

But this can lead unwanted results if any of the columns are numeric.
For instance:

   create table #joe (a int NOT NULL)
   go
   insert #joe SELECT 9 UNION SELECT 11 UNION SELECT 1023 UNION SELECT 87
   go
   select a FROM #joe ORDER BY CAST (a AS CHAR(4))

Returns:

   1023
   11
   87
   9

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

 
 
 

1. ado recordsets from stored procedures, stored procedures have input parameters

ado recordsets from stored procedures, stored procedures have input
parameters

i have a sql server (7.0) stored procedure

i want to create an ado recordset off the stored procedure

..

the .sql is

Create PROC usp_10_return_authors_upperlevel


AS

return
GO

and my current vb/ado code is

Private Sub Command1_Click()

On Error GoTo errorhandler:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial
Catalog=pubs;Data Source=."
Set rs = cn.Execute("EXEC usp_10_return_authors_upperlevel 'a'")

While Not rs.EOF
    MsgBox rs.Fields(0).Value
    rs.MoveNext
Wend

Set rs = Nothing
Set cn = Nothing

Exit Sub

errorhandler:
MsgBox Err.Number & " " & Err.Description

End Sub

..

it cracks on this line

Set rs = cn.Execute("EXEC usp_10_return_authors_upperlevel 'a'")

if i use

Set rs = cn.Execute("EXEC usp_10_return_authors_upperlevel"), it tells me i
don't have the parameter

how do i pass in the parameter??

..

sloan


been acting weird)

2. Can't Insert Record

3. temporary tables in stored procedures/parameter views vs stored procedures

4. Open Ingres

5. QUESTION: How can I call a Stored Procedure from another Stored Procedure

6. ODBC <-> long varchar on OS390

7. Calling a stored procedure with parameters from another stored procedure

8. Problem displaying all the rows in a grid.

9. ado recordsets from stored procedures, stored procedures have input parameters

10. output parameters stored procedures / extended stored procedures

11. Many Simple Stored Procedures VS. Few Complex Stored Procedures

12. Error at obtaining output parameters of the stored procedure, calling other stored procedure