[SQL] Access queries -> SQL Stored Procedures

[VanRoyal]

Ik heb een webapplicatie gemaakt op basis van ASP.NET en Access. Nu wil ik echter MSDE en/of MS SQL Server gaan gebruiken als database (ja, had ik natuurlijk vanaf het begin al moeten doen, maar de keuze voor Access had zo z'n redenen). Nu loop ik tegen een aantal problemen aan waar ik geen oplossing voor kan vinden; zowel via Google als in mijn SQL Server 2000 boek kan ik er niet echt iets over vinden.

Ik maak in de Access DB veel gebruik van queries; bijvoorbeeld om namen te formatteren/koppelen (voornaam, tussenvoegsels, achternaam, etc.), berekeningen te maken, etc. etc. Een voorbeeld van zo'n query:

code:

SELECT

tblDossier.dossierID,
tblDossier.DatumInvoer,
tblDossier.DatumGewijzigd,
tblDossier.gebruikerIDInvoer,
tblDossier.gebruikerIDGewijzigd,

[knip]

"D" & (Format([DatumInvoer],"mmyy")) & [dossierID] AS dossierNummer,
IIf([overigeKosten] Is Null,0,[overigeKosten]) AS overigeKosten1

FROM tblDossier;

Nu wil ik alle queries omzetten naar SQL Server views of Stored Procedures, maar ik kom er maar niet achter wat de 'Stored Procedure equivalent' van bijvoorbeeld onderstaande code is:

code:

"D" & (Format([DatumInvoer],"mmyy")) & [dossierID] AS dossierNummer

Wat ik wel heb gevonden is hoe je bijv. IIF's kunt vervangen door een CASE, alleen weet ik nog niet hoe ik dan de uitkomst van die CASE weer kan gebruiken.

Wie kan mij op weg helpen?

[Postman]

Geen idee of je FORMAT kunt gebruiken in SQL Server. Wat wel kan is een CAST. Hierover zal vast wel iets staan in je boek.

[P_de_B]

CONVERT kan de output van een datum formatteren als een string, die zou je kunnen gebruiken, in BooksOnline staat bij de CONVERT functie een uitgebreide beschrijving van de parameters. De syntax mogelijkheden van CASE staat daar ook goed in.


Overigens is het formatteren van bijvoorbeeld een datum in mijn ogen meer een taak voor de frontend dan voor de database.

[VanRoyal]

P_de_B schreef op 22 oktober 2004 @ 14:01:
CONVERT kan de output van een datum formatteren als een string, die zou je kunnen gebruiken, in BooksOnline staat bij de CONVERT functie een uitgebreide beschrijving van de parameters. De syntax mogelijkheden van CASE staat daar ook goed in.


Overigens is het formatteren van bijvoorbeeld een datum in mijn ogen meer een taak voor de frontend dan voor de database.

Ben ik met je eens, alleen formatteer ik hier niet zozeer een datum, maar maak ik een dossierNummer op basis van de invoerdatum en een autoincrement veld.

Ik zal eens gaan kijken in Books Online naar CAST en CONVERT.

[P_de_B]

iFlow schreef op 22 oktober 2004 @ 14:06:
[...]

Ben ik met je eens, alleen formatteer ik hier niet zozeer een datum, maar maak ik een dossierNummer op basis van de invoerdatum en een autoincrement veld.

Ik zal eens gaan kijken in Books Online naar CAST en CONVERT.

Kun je dan niet beter het veld al zo in de database opnemen? String concatenation is een vrij kostbare actie, en je kunt dan ook een index leggen op dat veld om het zoeken te versnellen.

[VanRoyal]

P_de_B schreef op 22 oktober 2004 @ 14:07:
[...]


Kun je dan niet beter het veld al zo in de database opnemen? String concatenation is een vrij kostbare actie, en je kunt dan ook een index leggen op dat veld om het zoeken te versnellen.

Ben ik in principe óók weer met je eens, maar ik kon geen 'makkelijke' manier vinden om dit te doen. Een autoincrement veld maken met een bepaalde format is - voor zover mijn kennis gaat - onmogelijk in Access.

Nu heb ik:

dossierID (autoincrement)
dossierNummer = "D" + yymm + dossierID

DossierNummer wordt in mijn applicatie over het algemeen gebruikt, omdat de query 'qryDossier' de tabel 'tblDossier' vervangt. Heb ik dossierID nodig ipv dossierNummer dan heb ik in code een functie die de eerste 4 karakters van dossierNummer afstript.

Ik had natuurlijk ook de primary key dossierID niet autoincrement kunnen maken, maar de waarde in code zelf kunnen maken bij het aanmaken van een nieuw record, maar dit lijkt me véél omslachtiger dan de manier die ik nu heb gekozen.

[VanRoyal]

Na een 'moeilijke start' heb ik even doorgezet en blijkt het allemaal minder lastig dan ik dacht. Of het de meest perfecte oplossing is weet ik niet, maar tot nu toe gaat het allemaal goed. Even een follow-up:

Het dossiernummer heb ik nu als volgt in een view gemaakt:

code:

'D' +
RIGHT (CAST(100 + DATEPART(MM; DatumInvoer) AS CHAR(3)); 2) +
RIGHT (CAST(DATEPART(YYYY; DatumInvoer) AS CHAR(4)); 2) +
CONVERT (char(10); dossierID)

Berekeningen (het gaat hier om een credit management applicatie, dus er wordt veel gerekend met bedragen) e.d. ga ik waarschijnlijk doen óf in de code óf in stored procedures en/of views. Het liefste doe ik het in stored procedures (of views?), dan kan ik namelijk de verschillende waarden gelijk gebruiken in alle delen van mijn applicatie (rapportages, MS Word merge documenten, overzichten, etc.). Ik weet alleen niet of dit programmatisch / technisch wel de beste oplossing is. Waar zouden jullie voor kiezen?