Hi!
I have a complex query that I had to put in a stored procedure because it is using parameters, and I was wondering whether it was possible to use the stored proc output as datasource?
If not, another solution would be to have a less complex query and to generate a view. However, a less complex query also mean that the output would not be ready to use and would need to be reworked programmatically. This would be possible, since the output of the query will never have more than a few hundred rows. In that case, I have a second question and a third question : Is it possible to reorganize the output of a datasource before it is used by a web control? If I call the view using code-behind, I will get a VList. I it possible to use a VList as datasource for a web control? Any example would be appreciated.
Here is my complex query in case you want to see it:
WITH Proximite (RowNumberC, RowNumberG, ClientGroupeID, ClientID, GroupeID, Latitude,
ClientNom, NomAliasEN, NomAliasFR, Longitude, Distance) AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY FC.ClientID ORDER BY ((FC.GroupeID - 1) % 3) ASC) AS RowNumberC,
ROW_NUMBER() OVER(PARTITION BY FC.GroupeID ORDER BY FC.ClientID ASC) AS RowNumberG,
ClientGroupeID, ClientID, GroupeID,
ClientNom, NomAliasEN, NomAliasFR,
Latitude, Longitude,
SQRT((@Latitude - Latitude)*(@Latitude - Latitude)+(@Longitude - Longitude)*(@Longitude - Longitude)) * @KM_PAR_DEGRE AS Distance
FROM dbo.FichesClient AS FC
WHERE ClientID <> @fk_ClientID
AND ABS(@Latitude - Latitude) < @DELTA_Y AND ABS(@Longitude - Longitude) < @DELTA_X
AND GroupeID IN (4, 5, 3)
)
SELECT ClientGroupeID, ClientID, GroupeID, Latitude,
ClientNom, NomAliasEN, NomAliasFR, Longitude, Distance
FROM Proximite
WHERE RowNumberC = 1 AND RowNumberG <= 5
ORDER BY RowNumberG ASC, Distance ASC
Cheers!
JF