0

What is the shortest way to do this? I can create a temporary table and then join the result, but is there a practical way to do it in one step? My procedure is complex, with multiple parameters and need to be executed each time.

CREATE PROCEDURE [dbo].[Get_Directory] 
    @Path VARCHAR(100) = 'c:\windows\temp\'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @rc INT;
    DECLARE @p VARCHAR(100) = 'dir /b '+ @path

    EXEC @rc = master..xp_cmdshell @p; 
END

SELECT * 
FROM table T 
LEFT JOIN (get_directory @path='c:\windows\temp\*') D ON D.output LIKE '%' + T.blabla + '%'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
fcm
  • 1,247
  • 15
  • 28
  • http://stackoverflow.com/questions/33833265/why-is-selecting-from-stored-procedure-not-supported-in-relational-databases – Lukasz Szozda Jan 18 '19 at 20:26
  • Have you considered a [Table Valued Function](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017#c-creating-a-multi-statement-table-valued-function)? – Adam Jan 18 '19 at 20:29
  • 2
    @Adam TVP won't work with stored procedure calls(i.e. master..xp_cmdshell) – Lukasz Szozda Jan 18 '19 at 20:30
  • Then no, you can't join onto a sproc. You'll need something to glue your data together. Be it dynamic SQL, `C#`, etc. – Adam Jan 18 '19 at 20:32
  • I mean, a backwards way would be a table parameter where you pass in the results from one query into the proc to join to. Probably perform terribly tho. – S3S Jan 18 '19 at 20:38
  • You can use a temp table, but this would make you alter the sp to insert its results into a temp table, and then you can join on that temp table. However, if you cannot alter the sp, you would have to use TVF or just replicate the sp query and use it in your query. – iSR5 Jan 18 '19 at 21:32
  • https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table You can insert the results from your stored procedure into a table, and then join on that. – pmbAustin Jan 18 '19 at 21:41

0 Answers0