heavy sp with heavy select - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

heavy sp with heavy select

Author

Topic

cecilius
Starting Member

19 Posts

Posted - 2002-09-14 : 09:21:05

i have a problem writing a sp for an auction site. In table a are some personal data and in b the auction data. I join these tables and have some criteria. two problems: The select uses a TOP-Clause with a variable, because i want to use this sp for paging purpose. Further more i should order the columns by seperate values, specified by variables, given to the sp.
The biggest problem is, that the paging is not working when there are more equal values in the ordered fiels (e.g. Records per Page = 10 and there are 30 auctions with 0$ in this order) than the amout of ask rows (TOP) is. How is it possible to get an absolute referring point in any table, independand of any order?

*********CODE**********
CREATE PROCEDURE SummeAuktion
( @Anzahl int, -- Records for TOP
@GruppenID int, -- Group-ID
@Start varchar(100), -- Startwert (Zaehler bei Zaehler, Titel bei Titel...)
@SortOrder varchar(30), -- Sort-Order, z.B. Title
@SortNr int, -- Number
@vSQL varchar(1000) -- dummy
)
AS
SELECT @vSQL = 'select TOP ' + convert(varchar, @Anzahl) + ' Auktionen.Zaehler as Zaehler, Auktionen.Titel as Titel,
Auktionen.Username as Anbieter, DATEDIFF(n, GETDATE(), DATEADD(n,Dauer,Start)) as Dauer, Auktionen.AktuellerPreis as AktuellerPreis, Auktionen.AnzahlGebote as Gebote,
Mitglieder.Ort as Ort, Auktionen.Beschreibung as Beschreibung, Auktionen.Startpreis as Startpreis, Auktionen.Start as Beginn from Auktionen, Mitglieder
where Auktionen.MUID = Mitglieder.MUID and Gruppe= ' + convert(varchar,@GruppenID) + ' and
Auktionen.Status<3 and DATEDIFF(n, GETDATE(), DATEADD(n,Dauer,Start)) > 0 and
not exists (select * from Gebote where AuktionsID = Auktionen.Zaehler and Agent = 2) and ' + convert(varchar, @SortOrder) + ' >='
+ convert(varchar, @Start) + ' order by ' + convert(varchar, @SortNr) + ' asc'
Execute(@vSQL)
Return

GO
*******CODE END********

Edited by - cecilius on 09/14/2002 09:41:31

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-14 : 11:15:19

One way to do this is by a temp table with an identity and the record IDs.

You can have a search SP to execute dynamic sql to populate the table with the max records needed - then you can split out into different SPs any common queries for speed.
Have another SP for the presentation which joins this table to the data for the select.

This will be slower than doing everything in dynamic sql but will make the code easily maintainable - but you look like you have a fairly simple structure so maybe don't need this.

You could change your query here to include a record ID (or whatever you have as a primary key for the auction item in all order by clauses and this will get round the same price problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cecilius
Starting Member

19 Posts

Posted - 2002-09-14 : 14:25:34

Hi, thx for posting. i am sorry, but i do not understand your suggestion concerning the same values. I have a primary key, but the problem is, that some rows may be missing (deleted), so the structue of the primay key when ordered by e.g. title may be unexpectable, because there may be titles beginning with C and Primary Key (int) 16 and titles beginning with D and Primary Key (int) 12. If i now order by title asc and start with primary key id 16 i will loss id 12.
i hope, you understand me. :-)
and, sorry, what do you mean with the multiple SPs? sould i execute SPs from one SP?
Thank you very much

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-09-15 : 03:09:43

Cecilius,

quote:

The biggest problem is, that the paging is not working when there are more equal values in the ordered fiels (e.g. Records per Page = 10 and there are 30 auctions with 0$ in this order)

Look up TOP in BOL..you will find ALL.

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources