0

I want to access outside the scope of a stored procedure a table variable that it defines. I have tried this one, but my case is different because my procedure receives a varchar as a parameter and then it creates a varchar that is executed, someway like this:

create procedure p_x
    @Table varchar(250)
as
begin
    declare @Sql varchar(450)

    select @sql = 'declare ' + @Table + 'table(col1 varchar(10), col2 float, col3 float, col4 float)'
    select @sql = @sql + ' insert' +  @Table + ' values('a', 1,1,1)'
    select @sql = @sql + ' insert' +  @Table + ' values('b', 2,2,2)'
    select @sql = @sql + ' select * from'

    execute(@sql) 
end
go

The solution the other question does is to pass as a parameter the new table to be processed. But in my case, because the code is written in a decisive part inside a varchar concatenation (and also it creates auxiliary tables concatenating at the end of the varchar @Table), I don't know what to do.

To give some context: there is this procedure that uses global temporary tables, which were called inside queries. Everything was working fine until we need to change the query to transform it into a table-valued function. The query just access the tables defined inside the procedure through global scope, but the table-valued function doesn't allow to access these global temporary tables. It seems that table variables can't be global.

In short, to change the query to a table-valued function, I need to change the procedure's temporary tables into table variables that I can access inside the table-valued function.

The big picture: 1) Today I have a query that works and this query calls a procedure.

2) I want to be able to call this query from a API without having to paste 100 lines of query

3) I received the suggestion of converting the query into a TFV

4) I did it, but it doesn't work, because TFV can't use temporary tables

5) I want a workaround to create a copy of the procedure with some minor changes that I can call from the TVF.

Matheus Popst de Campos
  • 139
  • 7
  • That doesn't make sense to me, you need to post an example of one of your functions to illustrate (words make a poor choice of explaining code) what you are trying to describe. – Dale K Sep 05 '19 at 20:24
  • Both the procedure and the query are above 100 lines. All you need to know is that instead of passing the table as a parameter, it passes varchar with the table name and then it is created inside the procedure. – Matheus Popst de Campos Sep 05 '19 at 20:28
  • So thats not possible using a table variable – Dale K Sep 05 '19 at 20:29
  • 2
    Why do you think that you need to use a table variable in order to convert a procedure to a TVF? – Tab Alleman Sep 05 '19 at 20:31
  • @TabAlleman I am not trying to convert the procedure to a TVF. I am trying to convert a query that calls a procedure into a TVF. – Matheus Popst de Campos Sep 05 '19 at 20:33
  • What I am looking for is a workaround to call the procedure (or a copy of it with some changes) from the TVF. What I've read on google is that it would be the path. – Matheus Popst de Campos Sep 05 '19 at 20:34
  • You want a TVF that calls a procedure? That doesn't make sense. You can't execute a procedure from inside a TVF. This has all the signs of an [xy problem](http://xyproblem.info/). What is it that you are really trying to do? – Sean Lange Sep 05 '19 at 20:34
  • 1) Today I have a query that works and this query calls a procedure. 2) I want to be able to call this query from a API without having to paste 100 lines of query 3) I received the suggestion of converting the query into a TFV 4) I did it, but it doesn't work, because TFV can't use temporary tables 5) I want a workaround to create a copy of the procedure with some minor changes that I can call from the TVF. – Matheus Popst de Campos Sep 05 '19 at 20:35
  • It sounds like a TVF is not going to be possible for you. You could make a stored procedure instead. If you post the entire actual query that you are trying to encompass, we might be able to give a definite answer. – Tab Alleman Sep 06 '19 at 13:32

1 Answers1

2

You cannot do this with a table variable. You can do it with a Temp Table, but table variables are automatically out of scope and inaccessible outside of the procedure that creates them.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • What I am solving is exactly the problem that the procedure is creating temporary tables inside of it, that are unaccessible from the table-valued function I am trying to convert the query into. – Matheus Popst de Campos Sep 05 '19 at 20:31
  • @MatheusPopstdeCampos thats why you need to provide further information, because that doesn't make sense. Are you calling the function within the same connection? Or from a new one? Cos temp tables are only valid on a single connection. – Dale K Sep 05 '19 at 20:34
  • I want to get rid of the temp tables. – Matheus Popst de Campos Sep 05 '19 at 20:44