Re: TOP statement doesn't accept a variable

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 13:41:34 -0700

Firest of all, read ISO-11179 so you will stop using those silly
prefixes on data element names. Your code looks like 1950's FORTRAN or
1960's BASIC and not SQL.

>> It looks like the use of a variable is not allowed in the TOP clause
of a select statement. <<

Yes, and since this a non-relational, proprietary feature it can have
any behavior MS wishes to give it, it can be changed at any time, etc.
This si one of many reasons that good programmers in any language avoid
proprietary features.

>> The following stored procedure is designed to return the
given row number from a query. <<

Well, the whole concept is wrong and you desparately need to read a book
on relational basics. SQL is a set-oriented language. Sets have no
ordering. There are no "row numbers" in a table. Tables are not a
sequential magnetic tape file.

>> If this is the case is there another way to make this stored
procedure work? <<

Use a cursor (which is what SELECT TOP n is under the covers) and go
back to 1950's file processing in SQL, as if you wre still using a 3GL.
Performance will stink and the code will not port; but you wil not have
to learn non-procedural, set-oriented programming which is the whole
idea of SQL.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Newbie help
    ... > reason I decided to store the data in a SQL Server DB. ... Then as the day goes on all tick data is simply inserted into ... >> with the most recent (as compared to the time parameter) non-null values ... Go back to basics. ...
    (microsoft.public.sqlserver.programming)
  • Re: How do i write Set based queries and avoid a cursor?
    ... We did that stuff with punch cards so we could slip in more cards ... somewhere in the schema. ... Learn to write good Standard SQL. ... The data element names change from the rest of the schema, ...
    (comp.databases.ms-sqlserver)
  • Re: DB Architecture Questions (for joe celko)
    ... No. Learn the ISO-11179 rules for data element names, ... Does your industry actually have "product_id" as a standard? ... After that, there is a natural key in the data, such as (longitude, ... That is why SQL has UPDATE, DRI actions and ALTER statements; ...
    (microsoft.public.sqlserver.programming)
  • Re: Newbie help
    ... I quoted the word real-time because in this case real-time means 1 tick ... the data in a SQL Server DB. ... Go back to basics. ... > do not work at that level, if you want to have any data integrity. ...
    (microsoft.public.sqlserver.programming)
  • Re: How to build a procedure that returns different numbers of columns as a result based on a parame
    ... maintain and violates some of basic ideas of RDBMS. ... SQL is not an application language; it is a data retrieval language. ... to cram everything into one SQL module. ... DeMarco, Myers, etc. and the basics of structured programming. ...
    (comp.databases.ms-sqlserver)