[SQL Server] Envoyer vers une table temporaire le resultat d'une requête construite dynamiquement
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 21/11/2016
-- Description: Envoyer vers une table temporaire le resultat d'une requête construite dynamiquement
-- =============================================
DECLARE @Requete NVARCHAR(MAX)
SELECT @Requete ='SELECT Nom,Prenom INTO #Resultat FROM Employes ORDER BY Nom'
EXECUTE sp_executesql@Requete
SELECT * FROM #Resultat
--Message D'erreur
(4 ligne(s) affectée(s))
Msg 208, Niveau 16, État 0, Ligne 26
Nom d'objet '#Resultat' non valide.
--Solution 1 :
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#Resultat'))
BEGIN
DROP TABLE #Resultat
END
CREATE TABLE #Resultat
(
Num_Lig INT IDENTITY(1,1)
,Nom VARCHAR(50) NULL
,Prenom VARCHAR(50) NULL
)
SELECT @Requete ='SELECT Nom,Prenom FROM Employes ORDER BY Nom'
INSERT INTO #Resultat(Nom,Prenom)
EXECUTE sp_executesql@Requete
SELECT * FROM #Resultat ORDER BY Num_Lig
--Solution 2 :
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#Resultat'))
BEGIN
DROP TABLE #Resultat
END
CREATE TABLE #Resultat
(
Num_Lig INT IDENTITY(1,1)
,Nom VARCHAR(50) NULL
,Prenom VARCHAR(50) NULL
)
SELECT @Requete ='INSERT INTO #Resultat SELECT Nom,Prenom FROM Employes ORDER BY Nom'
EXECUTE sp_executesql@Requete
SELECT * FROM #Resultat ORDER BY Num_Lig
--Solution 3 :
DECLARE @Requete NVARCHAR(MAX)
SELECT @Requete=N'
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N''[tempdb]..[#Resultat]''))
BEGIN
DROP TABLE #Resultat
END
CREATE TABLE #Resultat
(
BaseDonnees VARCHAR(150) NULL
,ROUTINE_NAME VARCHAR(150) NULL
)
EXEC sp_MSForEachDB
''USE ?;
INSERT INTO #Resultat SELECT DB_NAME() AS BaseDonnees, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ''''%MaProcedure%''''
AND ROUTINE_TYPE = ''''PROCEDURE''''''
SELECT * FROM #Resultat
DROP TABLE #Resultat'
EXEC(@Requete) AT [ServeurLie]