Insert Statement with EXEC and other Paramaters????

• Meatloaf

  SSCrazy

  Points: 2564

  More actions

  December 3, 2015 at 4:03 pm

  #305923

  Hi,

  Is it possible to create an INSERT STATMENT with an EXEC AND additional parameters?

  For example, I am trying to do the following:

  INSERT INTO #Temp (File_Dir, Files_In_Dir, DepthCnt, FileCnt)

  VALUES (@FileDir, EXECUTE master..xp_dirtree @FileDir, 1, 1)

  Is this possible with a different syntax?

• Eirikur Eiriksson

  SSC Guru

  Points: 182631

  More actions

  December 3, 2015 at 10:16 pm

  #1843972

  Meatloaf (12/3/2015)

  ---

  Hi,

  Is it possible to create an INSERT STATMENT with an EXEC AND additional parameters?

  For example, I am trying to do the following:

  INSERT INTO #Temp (File_Dir, Files_In_Dir, DepthCnt, FileCnt)

  VALUES (@FileDir, EXECUTE master..xp_dirtree @FileDir, 1, 1)

  Is this possible with a different syntax?

  Quick suggestion

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID(N'tempdb..#Temp1') IS NOT NULL DROP TABLE #Temp1;

  IF OBJECT_ID(N'tempdb..#Temp' ) IS NOT NULL DROP TABLE #Temp ;

  DECLARE @FileDir NVARCHAR(1024) = N'C:\SQLDATA';

  CREATE TABLE #Temp1

  (

  Id INT IDENTITY(1,1) NOT NULL

  ,Subdirectory NVARCHAR(512) NOT NULL

  ,Depth INT NOT NULL

  ,IsFile BIT NOT NULL

  );

  INSERT INTO #Temp1 (Subdirectory, Depth, IsFile)

  EXECUTE master..xp_dirtree @FileDir, 1, 1;

  SELECT

  T1.Id

  ,@FileDir AS File_Dir

  ,T1.Subdirectory

  ,T1.Depth

  ,T1.IsFile

  INTO #Temp

  FROM #Temp1 T1;

  SELECT

  T.Id

  ,T.File_Dir

  ,T.Subdirectory

  ,T.Depth

  ,T.IsFile

  FROM #Temp T;