Discussion :

[yanis97]

Bonjour;

Comment optimiser la structure d'une fonction (éviter la redondance du code). J'aimerais écrire la même structure qu'une procédure fonction en fonction du parametre s'execute telle action.
Exemple de fonctions :

CREATE FUNCTION dbo.F_USER_Obj (@SocieteID numeric(9), @UserID numeric(9), @CritNom varchar(50), @DateCourante char(8))
RETURNS @user TABLE (
SocieteID numeric(9),
USerID numeric(9),
Nom varchar(50),
Prenom varchar(50),
CiviliteID numeric(9),
CiviliteLib varchar(50),
Telephone varchar(20),
Fax varchar(20),
Email varchar(50),
)
AS
BEGIN
DECLARE @Nom varchar(50)
DECLARE @Prenom varchar(50)
DECLARE @CiviliteID numeric(9)
DECLARE @CiviliteLib varchar(50)
DECLARE @Telephone varchar(20)
DECLARE @Fax varchar(20)
DECLARE @Email varchar(50)

IF (@UserID IS NULL)
BEGIN
IF (@CritNom IS NULL)
BEGIN
DECLARE parcour_table CURSOR FOR SELECT USER.ENTI_NOK AS UserID,
ENTITE.ENTI_NOM AS Nom,
PERSONNE.PRS_PRENOM AS Prenom,
PERSONNE.CVLT_NOK AS CiviliteID,
CIVILITE.CVLT_Lib AS CiviliteLib,
USER.BENE_TEL AS Telephone,
USER.BENE_FAX AS Fax,
USER.BENE_EMAIL AS Email,
FROM dbo.USER (NOLOCK)
INNER JOIN dbo.PERSONNE (NOLOCK) ON (PERSONNE.ENTI_NOK=USER.ENTI_NOK)
INNER JOIN dbo.ENTITE (NOLOCK) ON (ENTITE.ENTI_NOK=USER.ENTI_NOK)
LEFT JOIN dbo.SALARIE (NOLOCK) ON ((SALARIE.SOC_ENTI_NOK=USER.SOC_ENTI_NOK) AND (SALARIE.ENTI_NOK=USER.ENTI_NOK))
LEFT JOIN dbo.ADRESSE AS ADR_FISC (NOLOCK) ON ((ADR_FISC.ENTI_NOK=USER.ENTI_NOK) AND (ADR_FISC.ADR_NOK=dbo.F_BENEF_AdrFisc(USER.SOC_ENTI_NOK,USER.ENTI_NOK,@DateCourante)))
LEFT JOIN dbo.ADRESSE AS ADR_POST (NOLOCK) ON ((ADR_POST.ENTI_NOK=USER.ENTI_NOK) AND (ADR_POST.ADR_NOK=dbo.F_BENEF_AdrPost(USER.SOC_ENTI_NOK,USER.ENTI_NOK,@DateCourante)))
LEFT JOIN dbo.PAYS AS PAYS_FISC (NOLOCK) ON (PAYS_FISC.PAYS_NOK=ADR_FISC.PAYS_NOK)
LEFT JOIN dbo.PAYS AS PAYS_POST (NOLOCK) ON (PAYS_POST.PAYS_NOK=ADR_POST.PAYS_NOK)
LEFT JOIN dbo.CIVILITE (NOLOCK) ON (CIVILITE.CVLT_NOK=PERSONNE.CVLT_NOK)
WHERE (USER.SOC_ENTI_NOK=@SocieteID)
END ELSE BEGIN
DECLARE parcour_table CURSOR FOR SELECT USER.ENTI_NOK AS USERID,
ENTITE.ENTI_NOM AS Nom,
PERSONNE.PRS_PRENOM AS Prenom,
PERSONNE.CVLT_NOK AS CiviliteID,
CIVILITE.CVLT_Lib AS CiviliteLib,
USER.BENE_TEL AS Telephone,
USER.BENE_FAX AS Fax,
USER.BENE_EMAIL AS Email,
FROM dbo.USER (NOLOCK)
INNER JOIN dbo.PERSONNE (NOLOCK) ON (PERSONNE.ENTI_NOK=USER.ENTI_NOK)
INNER JOIN dbo.ENTITE (NOLOCK) ON (ENTITE.ENTI_NOK=USER.ENTI_NOK)
LEFT JOIN dbo.SALARIE (NOLOCK) ON ((SALARIE.SOC_ENTI_NOK=USER.SOC_ENTI_NOK) AND (SALARIE.ENTI_NOK=USER.ENTI_NOK))
LEFT JOIN dbo.ADRESSE AS ADR_FISC (NOLOCK) ON ((ADR_FISC.ENTI_NOK=USER.ENTI_NOK) AND (ADR_FISC.ADR_NOK=dbo.F_BENEF_AdrFisc(USER.SOC_ENTI_NOK,USER.ENTI_NOK,@DateCourante)))
LEFT JOIN dbo.ADRESSE AS ADR_POST (NOLOCK) ON ((ADR_POST.ENTI_NOK=USER.ENTI_NOK) AND (ADR_POST.ADR_NOK=dbo.F_BENEF_AdrPost(USER.SOC_ENTI_NOK,USER.ENTI_NOK,@DateCourante)))
LEFT JOIN dbo.PAYS AS PAYS_FISC (NOLOCK) ON (PAYS_FISC.PAYS_NOK=ADR_FISC.PAYS_NOK)
LEFT JOIN dbo.PAYS AS PAYS_POST (NOLOCK) ON (PAYS_POST.PAYS_NOK=ADR_POST.PAYS_NOK)
LEFT JOIN dbo.CIVILITE (NOLOCK) ON (CIVILITE.CVLT_NOK=PERSONNE.CVLT_NOK)
WHERE ((USER.SOC_ENTI_NOK=@SocieteID) AND (ENTITE.ENTI_NOM LIKE @CritNom))
END
END ELSE BEGIN
IF (@CritNom IS NULL)
BEGIN
DECLARE parcour_table CURSOR FOR SELECT USER.ENTI_NOK AS USERID,
ENTITE.ENTI_NOM AS Nom,
PERSONNE.PRS_PRENOM AS Prenom,
PERSONNE.CVLT_NOK AS CiviliteID,
CIVILITE.CVLT_Lib AS CiviliteLib,
USER.BENE_TEL AS Telephone,
USER.BENE_FAX AS Fax,
USER.BENE_EMAIL AS Email,
FROM dbo.USER (NOLOCK)
INNER JOIN dbo.PERSONNE (NOLOCK) ON (PERSONNE.ENTI_NOK=USER.ENTI_NOK)
INNER JOIN dbo.ENTITE (NOLOCK) ON (ENTITE.ENTI_NOK=USER.ENTI_NOK)
LEFT JOIN dbo.SALARIE (NOLOCK) ON ((SALARIE.SOC_ENTI_NOK=USER.SOC_ENTI_NOK) AND (SALARIE.ENTI_NOK=USER.ENTI_NOK))
LEFT JOIN dbo.ADRESSE AS ADR_FISC (NOLOCK) ON ((ADR_FISC.ENTI_NOK=USER.ENTI_NOK) AND (ADR_FISC.ADR_NOK=dbo.F_BENEF_AdrFisc(USER.SOC_ENTI_NOK,USER.ENTI_NOK,@DateCourante)))
LEFT JOIN dbo.ADRESSE AS ADR_POST (NOLOCK) ON ((ADR_POST.ENTI_NOK=USER.ENTI_NOK) AND (ADR_POST.ADR_NOK=dbo.F_BENEF_AdrPost(USER.SOC_ENTI_NOK,USER.ENTI_NOK,@DateCourante)))
LEFT JOIN dbo.PAYS AS PAYS_FISC (NOLOCK) ON (PAYS_FISC.PAYS_NOK=ADR_FISC.PAYS_NOK)
LEFT JOIN dbo.PAYS AS PAYS_POST (NOLOCK) ON (PAYS_POST.PAYS_NOK=ADR_POST.PAYS_NOK)
LEFT JOIN dbo.CIVILITE (NOLOCK) ON (CIVILITE.CVLT_NOK=PERSONNE.CVLT_NOK)
WHERE ((USER.SOC_ENTI_NOK=@SocieteID) AND (USER.ENTI_NOK=@USERID))
END ELSE BEGIN
DECLARE parcour_table CURSOR FOR SELECT USER.ENTI_NOK AS USERID,
ENTITE.ENTI_NOM AS Nom,
PERSONNE.PRS_PRENOM AS Prenom,
PERSONNE.CVLT_NOK AS CiviliteID,
CIVILITE.CVLT_Lib AS CiviliteLib,
USER.BENE_TEL AS Telephone,
USER.BENE_FAX AS Fax,
USER.BENE_EMAIL AS Email,
FROM dbo.USER (NOLOCK)
INNER JOIN dbo.PERSONNE (NOLOCK) ON (PERSONNE.ENTI_NOK=USER.ENTI_NOK)
INNER JOIN dbo.ENTITE (NOLOCK) ON (ENTITE.ENTI_NOK=USER.ENTI_NOK)
LEFT JOIN dbo.SALARIE (NOLOCK) ON ((SALARIE.SOC_ENTI_NOK=USER.SOC_ENTI_NOK) AND (SALARIE.ENTI_NOK=USER.ENTI_NOK))
LEFT JOIN dbo.ADRESSE AS ADR_FISC (NOLOCK) ON ((ADR_FISC.ENTI_NOK=USER.ENTI_NOK) AND (ADR_FISC.ADR_NOK=dbo.F_BENEF_AdrFisc(USER.SOC_ENTI_NOK,USER.ENTI_NOK,@DateCourante)))
LEFT JOIN dbo.ADRESSE AS ADR_POST (NOLOCK) ON ((ADR_POST.ENTI_NOK=USER.ENTI_NOK) AND (ADR_POST.ADR_NOK=dbo.F_BENEF_AdrPost(USER.SOC_ENTI_NOK,USER.ENTI_NOK,@DateCourante)))
LEFT JOIN dbo.PAYS AS PAYS_FISC (NOLOCK) ON (PAYS_FISC.PAYS_NOK=ADR_FISC.PAYS_NOK)
LEFT JOIN dbo.PAYS AS PAYS_POST (NOLOCK) ON (PAYS_POST.PAYS_NOK=ADR_POST.PAYS_NOK)
LEFT JOIN dbo.CIVILITE (NOLOCK) ON (CIVILITE.CVLT_NOK=PERSONNE.CVLT_NOK)
WHERE ((USER.SOC_ENTI_NOK=@SocieteID) AND (USER.ENTI_NOK=@USERID) AND (ENTITE.ENTI_NOM LIKE @CritNom))
END
END

OPEN parcour_table
FETCH NEXT FROM parcour_table INTO @USERID, @Nom, @Prenom, @CiviliteID, @CiviliteLib, @Telephone, @Fax, @Email,
WHILE @@FETCH_STATUS = 0
BEGIN
IF @@FETCH_STATUS <> -2
BEGIN
INSERT INTO @USER (SocieteID, USERID, Nom, Prenom, CiviliteID, CiviliteLib, Telephone, Fax, Email) VALUES (@SocieteID, @USERID, @Nom, @Prenom, @CiviliteID, @CiviliteLib, @Telephone, @Fax, @Email, END
FETCH NEXT FROM parcour_table INTO @USERID, @Nom, @Prenom, @CiviliteID, @CiviliteLib, @Telephone, @Fax, @Email) END
CLOSE parcour_table
DEALLOCATE parcour_table
RETURN
END

Merci;

[Fabien Celaia]

C'est hyper lourd.

Pourquoi un curseur dans une fonction et pas un "simple" INSERT ... SELECT dans une SP ?

Evitez l'usage des fonctions dans les conditions : ca pourri l'utilisation d'éventuels indexes.