0

I have this stored procedure

CREATE PROCEDURE Research
    (@ExamenId INT = NULL)
AS  
BEGIN  
    CREATE TABLE #EXAMENS (PRESC_ID int, DEMANDE_ID int, EXAMEN_ID int)  

    IF (@ExamenId IS NOT NULL)   
    BEGIN  
        INSERT INTO #EXAMENS (PRESC_ID, DEMANDE_ID, EXAMEN_ID)
            SELECT 
                OPI.PRESC_ID, PRPED.DEMANDE_ID, PRPE.EXAMEN_ID  
            FROM 
                PG_RADIO_PRESC_EXAMEN PRPE WITH (NOLOCK)  
            INNER JOIN 
                OPIUM_PRESC_ITEM OPI WITH (NOLOCK) ON OPI.ITEM_ID = PRPE.ITEM_ID  
            LEFT JOIN  
                PG_RADIO_PRESC_EXD_DEM PRPED WITH (NOLOCK) ON PRPED.EXAMEN_ID = PRPE.EXAMEN_ID  
            WHERE 
                PRPE.EXAMEN_ID = @ExamenId  
    END  

    SELECT
        tes.EXAMEN_ID, tes.PARAMETRE_ID,
        TXT_CODE_ID, VALEUR
    FROM
        (SELECT 
             PRPE.EXAMEN_ID, PRPEG.ParametreEntreeId AS PARAMETRE_ID,
             0 AS TXT_CODE_ID,
             ISNULL(Replace(CAST(pfs.FORM_SOURCE_SAISIE AS VARCHAR(MAX)), '', ''), Replace(CAST(pf.FORM_SOURCE AS VARCHAR(MAX)), '', '')) AS VALEUR
         FROM 
             #EXAMENS tmpexam WITH (NOLOCK)  
         INNER JOIN 
             PG_RADIO_PRESC_EXAMEN PRPE WITH (NOLOCK) ON PRPE.EXAMEN_ID = tmpexam.EXAMEN_ID  
         INNER JOIN 
             PG_RADIO_PARAMETRE_ENTREE_GESTE PRPEG ON PRPEG.GesteId = PRPE.GESTE_ID  
         INNER JOIN
             PG_PARAM_ENTREE_FORMULAIRE pf ON pf.PARAMETRE_ID = PRPEG.ParametreEntreeId  
         LEFT JOIN
             PG_PARAM_ENTREE_FORMULAIRE_SAISI pfs ON pfs.EXAMEN_ID = tmpexam.EXAMEN_ID   
                                                  AND pfs.PARAMETRE_ID = PRPEG.ParametreEntreeId) AS tes
    LEFT JOIN
        PG_PARAM_ENTREE_PARAMETRE ps ON ps.PARAMETRE_ID = tes.PARAMETRE_ID 
    ORDER BY 
        ps.RANG ASC
END 

It returns EXAMEN_ID, PARAMETRE_ID, TXT_CODE_ID, VALEUR for one exam.

Now I want to pass a list of ids as varchar and for each id return its information EXAMEN_ID, PARAMETRE_ID, TXT_CODE_ID, VALEUR.

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hamza
  • 49
  • 8
  • Possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](https://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) – Tab Alleman Nov 27 '17 at 19:12
  • You should check out **table-valued parameters** instead – marc_s Nov 27 '17 at 19:18
  • You might also want to check out this article. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – Sean Lange Nov 27 '17 at 19:25

0 Answers0