[SQL SERVER] select top @param * from... kan dat?

[admiral866]

ok, ik heb niet bijster veel ervaring met stored procedures. Maar ik kom er niet uit waarom het volgende niet ok is.

code:

CREATE PROCEDURE sp_naam(@selaantal int)
AS
select top @selaantal items.id, items.itemcode from items

Er komt dan een syntax error

Line 3: Incorrect syntax near '@selaantal'.

Is het mogelijk om op een dergelijke plaats, dus na "top" een parameter op te nemen? Of moet er nog iets extra's gebeuren?

[whoami]

Neen, dat kan niet. Parameters kan je enkel gebruiken voor variablen in de WHERE clausule.
Wat jij wil doen, zal je mbhv string concatenation moeten doen.

_{Het is trouwens geen goed idee om 'custom' stored procedures met de sp_ prefix te beginnen. Indien je dit doet, zal Sql Server er nl. vanuit gaan dat het een system SP betreft, en deze dus eerst in de master DB gaan zoeken. Die zal hij daar natuurlijk niet vinden, en dan pas zal hij in de current DB gaan kijken. Hier zit je dus met een performance hit.}

[ Voor 55% gewijzigd door whoami op 06-06-2006 17:01 ]

[RobIII]

En wat whoami bedoelt is iets als:

SQL:

CREATE PROCEDURE sp_naam(@selaantal int)
AS

DECLARE @MyQuery as varchar(1024)

SET @MyQuery = 'Select top ' + STR(@selaantal) + ' items.id, items.itemcode from items'

EXEC(@MyQuery)

Edit: Waarbij de haakjes bij EXEC belangrijk zijn om niet te vergeten

Pas trouwens wel op voor SQL injection en dat soort ongein (nu is dat risico voor jou nu nihil omdat je een int gebruikt, maar als je strings gaat gebruiken wordt het al gevaarlijk(er) )

[ Voor 78% gewijzigd door RobIII op 06-06-2006 17:04 ]

[admiral866]

ah ok, hartstikke bedankt.
En ik had de query even versimpeld, in het "echt" heet ie wel anders etc

[gorgi_19]

Waarom wil je hier een SP voor gebruiken en bouw je je query niet dynamisch op?

Levert het zetten van een RowCount trouwens geen betere performance op in dit specifieke geval?

[EfBe]

gorgi_19 schreef op dinsdag 06 juni 2006 @ 17:23:
Waarom wil je hier een SP voor gebruiken en bouw je je query niet dynamisch op?

Levert het zetten van een RowCount trouwens geen betere performance op in dit specifieke geval?

Er is een verschil tussen TOP en RowCount, zie BOL (bv). Heeft te maken met de selectie van de uiteindelijke resultset bij een query met een orderby clause.

En SQL concatenaten IN een stored procedure is niet bijster handig. Ten eerste levert het een trage proc op (het executoin plan wordt niet gecached, tenzij in de proc exec sp_executesql gebruikt wordt) en ten tweede is SQL niet geschikt voor string operaties, het is een set language. Doe dus die concatenatie in je client code (en parameters gebruiken (jaja behalve voor die TOP natuurlijk ))

[ Voor 32% gewijzigd door EfBe op 06-06-2006 19:19 ]

[admiral866]

hm ok Ik moet nog even testen of het performance wise wat oplevert. Maar als ik 't zo lees is dat ook niet echt nodig, Zo heel veel aanvragen zijn er niet dat het echt veel zal uitmaken denk ik.

[giMoz]

maar wat als je nou echt stp's wil gebruiken (bijv omdat de gebruiker van je applicatie alleen maar execute rechten heeft op stp's en nix op tabellen) is er dan nog een mogelijkheid om dynamisch een "top" te doen??? (en ook niet d.m.v. exec string meuk)

[P_de_B]

Met RowCount zou het kunnen, je kunt SET ROWCOUNT @Variabele doen. Er zijn echter zoals eFBe zegt wel verschillen met TOP. Zie BooksOnline.

[admiral866]

"Mijn" top wordt gebruikt icm met nog een andere top in een sub select, en asc/desc order by's.
Om paginering na te bootsen zeg maar.

[Woy]

Ik dacht gelezen te hebben dat je in SQL Server 2005 wel een top met een variabele kon doen. Maar weet dat even niet zeker meer.

[RobIII]

rwb schreef op dinsdag 06 juni 2006 @ 23:00:
Ik dacht gelezen te hebben dat je in SQL Server 2005 wel een top met een variabele kon doen. Maar weet dat even niet zeker meer.

Hmmm, daar ben ik dan benieuwd naar. Ik zal morgen eens kijken of dat kan (op de zaak draait een 2005 )

[admiral866]

hm kan die "SET ROWCOUNT @param" ook met een paramete?
Zit te denken om dan maar dat te doen en dan WHERE ID > [hoogste_id_van_vorige_result] en dan orderby ID ASC.
Voor paginering doeleinden. Zo hoef ik geen string concatenating te doen.

edit:
ah ok...

One of the primary advantages of SET ROWCOUNT is that you can use a variable to specify the number of rows; with SET TOP you cannot. So if you want to create a stored procedure that takes the row count as a parameter, you can only use SET ROWCOUNT like this:

http://www.devx.com/getHelpOn/10MinuteSolution/20564

[ Voor 44% gewijzigd door admiral866 op 07-06-2006 09:10 ]

[P_de_B]

RobIII schreef op woensdag 07 juni 2006 @ 00:31:
[...]

Hmmm, daar ben ik dan benieuwd naar. Ik zal morgen eens kijken of dat kan (op de zaak draait een 2005 )

Dat werkt: http://msdn2.microsoft.co...ary/ms189463(SQL.90).aspx

[P_de_B]

admiral schreef op woensdag 07 juni 2006 @ 09:08:
hm kan die "SET ROWCOUNT @param" ook met een paramete?
Zit te denken om dan maar dat te doen en dan WHERE ID > [hoogste_id_van_vorige_result] en dan orderby ID ASC.
Voor paginering doeleinden. Zo hoef ik geen string concatenating te doen.

edit:
ah ok...

[...]

http://www.devx.com/getHelpOn/10MinuteSolution/20564

Dat zei ik toch al?

[admiral866]

P_de_B schreef op woensdag 07 juni 2006 @ 09:55:
[...]

Dat zei ik toch al?

true
had nog geen koffie gehad

[gorgi_19]

admiral schreef op woensdag 07 juni 2006 @ 09:08:
hm kan die "SET ROWCOUNT @param" ook met een paramete?
Zit te denken om dan maar dat te doen en dan WHERE ID > [hoogste_id_van_vorige_result] en dan orderby ID ASC.
Voor paginering doeleinden. Zo hoef ik geen string concatenating te doen.
http://www.devx.com/getHelpOn/10MinuteSolution/20564

http://www.4guysfromrolla.com/webtech/042606-1.shtml is ook een optie