-1

The execution of the Stored Procedure below results in the following output

enter image description here

The above output isn't create in a view or a table that can be queried later.

Can someone show me how to modify the procedure such that the output results in the creation of table called, say dbo.mytesttable

The Stored Procedure is as follows:

CREATE PROC outbound.usp_Terminals(@AsOfDate date)

AS

SELECT  
    airport_code,
    airport_name,
    airport_city,
    airport_state
FROM 
    dbo.fnGetAirports(@AsOfDate)

GO

A friend suggested the following code, but it didn't work:

DECLARE @date date
SET @date = ...

INSERT INTO #prebuilt_table
EXEC dbo.outbound.usp_Terminals @date
Patterson
  • 1,927
  • 1
  • 19
  • 56
  • Use `select ... into... from...` – Stu Jun 23 '23 at 15:34
  • 2
    Do you want to create the table in the procedure, or use the output of the procedure to create the table? It's unclear. Why do you need a procedure for this anyway? – Thom A Jun 23 '23 at 15:36
  • Hi, I would like to use the output of the procedure to create the table please. – Patterson Jun 23 '23 at 15:43
  • You asked *Can someone show me how to modify the procedure* which contradicts *use the output of*? – Stu Jun 23 '23 at 15:48
  • Hi @Stu, sorry for my mis-interpretation of the question – Patterson Jun 23 '23 at 15:52
  • What is the point of a stored proc that just selects a subset of columns from a TVF? If you want this in a tabular form ("view or a table") why can't you just reference the TVF? – Martin Smith Jun 23 '23 at 15:53
  • 2
    You cannot use the output of a procedure to `CREATE` a table, @Patterson ; there is no `EXEC ... INTO` or `SELECT ... INTO ... EXEC` syntax. You *might* be able to "cheat" with `OPENROWSET` or something, but parametrisation becomes impossible then. Why not just directly call your (hopefully **inline**) table value fucntion `dbo.fnGetAirports`? Then you can just do `SELECT ... INTO ... FROM dbo.fnGetAirports(@AsOfDate);`. – Thom A Jun 23 '23 at 15:57
  • So, it's not possible to modify the procedure such that it creates a table from the results of the output? – Patterson Jun 23 '23 at 16:12
  • I thought something like what @Stu mentioned would work? – Patterson Jun 23 '23 at 16:40
  • Hi, I just saw the following from this site https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table?rq=2 would that not work in my case? – Patterson Jun 23 '23 at 17:04
  • 1
    Why on earth would you want to do that when you can just reference the TVF directly? What is the perceived benefit that the stored proc is giving you here? – Martin Smith Jun 23 '23 at 17:07
  • Hi @MartinSmith, I appreciate it seems odd, but the reason is because I want to add the Stored Procedure to an Azure Data Factory Stored Procedure activity. And I stored to parameterize the AsOfDate function. Azure Data Factory does not support TVF's – Patterson Jun 23 '23 at 17:12
  • @MartinSmith, I didn't want to get into the weeds of this, but as you asked - once it's stored into a table it will be picked up by Databricks, and databricks needs to see it in tabular form. – Patterson Jun 23 '23 at 17:19
  • Pre-build the table. If columns are variable always include them, but return NULLs as apprpropriate. Include an error message in your question if this fails – Patrick Hurst Jun 24 '23 at 10:05
  • Can DataBricks not pick up a view? – Charlieface Jun 25 '23 at 10:39

1 Answers1

0

There are a number of solutions, the simplest of the solutions is as follows:

select 
    airport_code,
    airport_name,
    airport_city,
    airport_state
into MyTableName
from dbo.fnGetAirports(@AsOfDate)
Patterson
  • 1,927
  • 1
  • 19
  • 56