Bonsoir,

Pour récupérer la Nième ligne d'une table, j'utilise la requête suivante :

SELECT TOP 1
C0 as _C0, C1 as _C1,C2 as _C2,C3 as _C3,C4 as _C4,
C5 as _C5,C6 as _C6,C7 as _C7,C8 as _C8,C9 as _C9,
C10 as _C10,C11 as _C11,C12 as _C12,C13 as _C13,C14 as _C14,
C15 as _C15,C16 as _C16,C17 as _C17,C18 as _C18,C19 as _C19,
C20 as _C20,C21 as _C21,C22 as _C22,C23 as _C23,C24 as _C24,
C25 as _C25,C26 as _C26,C27 as _C27,C28 as _C28,C29 as _C29,
C30 as _C30,C31 as _C31,C32 as _C32,C33 as _C33,C34 as _C34,
C35 as _C35,C36 as _C36,C37 as _C37,C38 as _C38
FROM (
SELECT TOP ? *
FROM [dbo].[MA_TABLE]
ORDER BY [ID]
) T1
ORDER BY [ID] DESC

Pour etre dynamique, j'ai une variable globale (_indice) dans mon lot DTS.
Cependant l'assistant DTS ne me permet pas de faire le lien entre ? et
_indice.

j'ai un message d'erreur qui me dit :
Source de l’erreur : Microsoft OLE DB Provider for SQL Server
Description de l’erreur : Erreur de syntaxe ou violation d’accès

Cordialement,

DANS une procédure stockée :

CREATE PROCEDURE P_xxx @LIGNES INT
AS

SELECT TOP 1
C0 as _C0, C1 as _C1,C2 as _C2,C3 as _C3,C4 as _C4,
C5 as _C5,C6 as _C6,C7 as _C7,C8 as _C8,C9 as _C9,
C10 as _C10,C11 as _C11,C12 as _C12,C13 as _C13,C14 as _C14,
C15 as _C15,C16 as _C16,C17 as _C17,C18 as _C18,C19 as _C19,
C20 as _C20,C21 as _C21,C22 as _C22,C23 as _C23,C24 as _C24,
C25 as _C25,C26 as _C26,C27 as _C27,C28 as _C28,C29 as _C29,
C30 as _C30,C31 as _C31,C32 as _C32,C33 as _C33,C34 as _C34,
C35 as _C35,C36 as _C36,C37 as _C37,C38 as _C38
FROM (
SELECT TOP (@LIGNES) *
FROM [dbo].[MA_TABLE]
ORDER BY [ID]
) T1
ORDER BY [ID] DESC

GO

Mieux :


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS N,
C0 as _C0, C1 as _C1,C2 as _C2,C3 as _C3,C4 as _C4,
C5 as _C5,C6 as _C6,C7 as _C7,C8 as _C8,C9 as _C9,
C10 as _C10,C11 as _C11,C12 as _C12,C13 as _C13,C14 as _C14,
C15 as _C15,C16 as _C16,C17 as _C17,C18 as _C18,C19 as _C19,
C20 as _C20,C21 as _C21,C22 as _C22,C23 as _C23,C24 as _C24,
C25 as _C25,C26 as _C26,C27 as _C27,C28 as _C28,C29 as _C29,
C30 as _C30,C31 as _C31,C32 as _C32,C33 as _C33,C34 as _C34,
C35 as _C35,C36 as _C36,C37 as _C37,C38 as _C38
FROM dbo.MA_TABLE
) T
WHERE N = 78


A +

Cedric a écrit :
> Bonsoir,
>
> Pour récupérer la Nième ligne d'une table, j'utilise la requête suivante :
>
> SELECT TOP 1
> C0 as _C0, C1 as _C1,C2 as _C2,C3 as _C3,C4 as _C4,
> C5 as _C5,C6 as _C6,C7 as _C7,C8 as _C8,C9 as _C9,
> C10 as _C10,C11 as _C11,C12 as _C12,C13 as _C13,C14 as _C14,
> C15 as _C15,C16 as _C16,C17 as _C17,C18 as _C18,C19 as _C19,
> C20 as _C20,C21 as _C21,C22 as _C22,C23 as _C23,C24 as _C24,
> C25 as _C25,C26 as _C26,C27 as _C27,C28 as _C28,C29 as _C29,
> C30 as _C30,C31 as _C31,C32 as _C32,C33 as _C33,C34 as _C34,
> C35 as _C35,C36 as _C36,C37 as _C37,C38 as _C38
> FROM (
> SELECT TOP ? *
> FROM [dbo].[MA_TABLE]
> ORDER BY [ID]
> ) T1
> ORDER BY [ID] DESC
>
> Pour etre dynamique, j'ai une variable globale (_indice) dans mon lot DTS.
> Cependant l'assistant DTS ne me permet pas de faire le lien entre ? et
> _indice.
>
> j'ai un message d'erreur qui me dit :
> Source de l’erreur : Microsoft OLE DB Provider for SQL Server
> Description de l’erreur : Erreur de syntaxe ou violation d’accès
>
> Cordialement,


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************