Transferring tabular data from stored procedure
It will be about methods of obtaining the results of the procedure in the form of tables for further work with them in SQL. I think the majority of what is stated here can be useful only in applications with complex SQL logic and lengthy procedures. I do not presume to say that these methods are the most effective. This is just what I use in my work. All this works under Microsoft SQL Server 2008.
To those who are familiar with the topic, I propose to scroll through the post to the fifth method.
Let the procedure from which we need to obtain data be:
One of the easiest methods. We use the
By writing to a previously created table. Here you have to add insert to the procedure:
In fact, we moved the insert string inside the procedure.
I do not consider the possibility of passing through permanent tables, because if required, the task is not to relate to this topic. If not, then we get unnecessary problems with blocking and identification between sessions.
In essence, it is a refinement of the second method. To simplify support, create a custom table type. It looks like this:
')
The complication of the third method, which allows you to create a table with constraints and indexes. In difference from previous works under Microsoft SQL Server 2005.
However, usually the delmy time column is not used, instead the table is created simply with one first column (here with p1).
This method uses pre-created procedures. It is based on the inclusion of a dynamic SQL query in the procedure being run. However, it is quite easy to use.
To use it, the procedure must be processed as follows:
1. At the beginning of the procedure include the lines:
2. Alter all output selects of the procedure to create temporary tables starting with
3. At the end of the procedure, include the line:
For our example, we get:
The launch is carried out as follows:
Since the generated SQL is not always good, the given example is better suited for small instructions. If there is a lot of code, you can either put it into a separate procedure and from the dynamic part make only an exec call, or reload the data into new temporary tables. In the latter case, of course, there is another “extra” copying, but it often happens that at this stage we can group the result and select only the necessary fields for further processing (for example, if in any case all the returned data is not required) ).
The
The work is structured in such a way that the launch of
You can use the parameter transfer from the function (
There is an article on this topic.
I don’t see the point of using the cursor for this task, only if the cursor is initially required. But XML looks promising. Here are very interesting results of performance tests.
It is interesting to hear how you use ways to simplify this task :)
UPD 03/31/2014: Adjusted the post on the ideas from the comments
To those who are familiar with the topic, I propose to scroll through the post to the fifth method.
Let the procedure from which we need to obtain data be:
create procedure Proc1 as begin select 1 p1, 'b' p2 end 1 Method
One of the easiest methods. We use the
insert ... exec ... construction insert ... exec ... if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 create table #t1(p1 int, p2 varchar(max)) insert #t1 exec Proc1 select * from #t1 Advantages and disadvantages:
- The fields transferred are listed 2 times (these are internal
select, external table creation andinsert). Plus, the enumeration of fields occurs with each new similar call. (I add this criterion, because with a large number of revisions and a set of places to call a procedure, the process of changing the output data becomes very time consuming) - It has a serious limitation - we can only get one table.
- To
exec Proc1procedure in the simple output mode, no additional actions are required, it is enough to runexec Proc1withoutinsert
2 Method
By writing to a previously created table. Here you have to add insert to the procedure:
create procedure Proc1 as begin insert #t1(p1, p2) select 1 p1, 'b' p2 end In fact, we moved the insert string inside the procedure.
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 create table #t1(p1 int, p2 varchar(max)) exec Proc1 select * from #t1 Advantages and disadvantages:
- Transferred fields are listed 2 times, and one more transfer for each new use
- For the procedure to work in simple output mode, you will need to either write a separate procedure that
Proc1tables received fromProc1, or determine when to output them insideProc1. For example, based on the existence of a table for insertion:
alter procedure Proc1 as begin declare @show bit if object_id(N'tempdb..#t1',N'U') is null begin set @show = 1 create table #t1(p1 int, p2 varchar(max)) end insert #t1(p1, p2) select 1 p1, 'b' p2 if (@show = 1) begin select * from #t1 end end I do not consider the possibility of passing through permanent tables, because if required, the task is not to relate to this topic. If not, then we get unnecessary problems with blocking and identification between sessions.
3 Method
In essence, it is a refinement of the second method. To simplify support, create a custom table type. It looks like this:
create type tt1 as table(p1 int, p2 varchar(max)) go create procedure Proc1 as begin insert #t1(p1, p2) select 1 p1, 'b' p2 end go -- : declare @t1 tt1 if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 select * into #t1 from @t1 exec Proc1 select * from #t1 Advantages and disadvantages:
- Transmitted fields are listed 2 times, with each new use does not add complexity
- Organizing the direct output of the result also requires additional actions.
- There are some difficulties with creating indexes and restrictions, since we cannot create them with
select ... intoconstructionsselect ... into
')
4 Method
The complication of the third method, which allows you to create a table with constraints and indexes. In difference from previous works under Microsoft SQL Server 2005.
create procedure Proc1 as begin insert #t1(p1, p2) select 1 p1, 'b' p2 end go create procedure Proc1_AlterTable as begin alter table #t1 add p1 int, p2 varchar(max) alter table #t1 drop column delmy end go -- : if object_id(N'tempdb..#t1',N'U') is not null drop table #t1 create table #t1(delmy int) exec Proc1_AlterTable exec Proc1 select * from #t1 However, usually the delmy time column is not used, instead the table is created simply with one first column (here with p1).
Advantages and disadvantages:
- Transmitted fields are listed 2 times, with each new use does not add complexity
- For immediate output of the result, additional actions are also required.
- It was unexpectedly discovered that sometimes, for unknown reasons, there are locks on the
alter table #t1structure, and the process waits for the complete completion of theProc1(notProc1_AlterTable!) Parallel query. If anyone knows what this is connected with - share, I will be glad to hear :)
5 Method
This method uses pre-created procedures. It is based on the inclusion of a dynamic SQL query in the procedure being run. However, it is quite easy to use.
To use it, the procedure must be processed as follows:
1. At the beginning of the procedure include the lines:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin 2. Alter all output selects of the procedure to create temporary tables starting with
#Output (for example into #Output , into #Output5 , into #OutputMySelect ). If the procedure does not create a result set, then no action is required.3. At the end of the procedure, include the line:
exec util.InclusionEnd -- , #Output, util.InclusionBegin For our example, we get:
create procedure Proc1 as begin if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin select 1 p1, 'b' p2 into #Output1 exec util.InclusionEnd -- , #Output, util.InclusionBegin end The launch is carried out as follows:
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max)) exec util.InclusionRun' select * from #InclusionOutput1 ', 1, '#InclusionOutput' exec Proc1 Since the generated SQL is not always good, the given example is better suited for small instructions. If there is a lot of code, you can either put it into a separate procedure and from the dynamic part make only an exec call, or reload the data into new temporary tables. In the latter case, of course, there is another “extra” copying, but it often happens that at this stage we can group the result and select only the necessary fields for further processing (for example, if in any case all the returned data is not required) ).
The
util.InclusionRun functions are passed 3 parameters:@sql- SQL script that executes inside the called procedure@notShowOutput- if = 1, then block output of tables starting with#Output@replaceableTableName- (default ='#Output') set the prefix in the name of the tables used in@sql, to replace it with the corresponding#Output*table in the script. For example, if you specify#InclusionOutput, and two tables#Output55and#Output0Acreated in the procedure, in@sqlyou can refer to#Output55as#InclusionOutput1, and#Output0Aas#InclusionOutput2
The work is structured in such a way that the launch of
Proc1 , without first running util.InclusionRun leads to the natural work of the procedure with the output of all the data that it displayed before processing.Nuances of use:
- Imposes restrictions on the use of the
returnin the procedure, since it needs to be startedutil.InclusionEnd - Outputting selects from the launched procedures output the result earlier than even those # Output-tables that were created before they were called (this is logical, since the output occurs only in
util.InclusionEnd)
Advantages and disadvantages:
- Transmitted fields are listed once, with each new use not adding complexity.
- No direct action is required to display the result.
- It is necessary to remember and consider the nuances of use.
- Due to additional procedures, more instructions are executed, which can reduce the speed of frequent calls (I think that this can be neglected at startup less than once per second)
- Perhaps, it may complicate the understanding of the code for employees who are not familiar with this method: the procedure acquires two exec-calls and the non-obviousness that all
#Outputtables will be displayed - Allows you to easily organize unit testing without external tools
Demonstration of use:
Hidden text
Code:
Result:
if object_id('dbo.TestInclusion') is not null drop procedure dbo.TestInclusion go create procedure dbo.TestInclusion @i int as begin if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin if object_id('tempdb..#tmp2', 'U') is not null drop table #tmp2 select @i myI into #tmp2 if object_id('tempdb..#tmp3', 'U') is not null drop table #tmp3 select @i + 1 myI into #tmp3 select * into #Output0 -- ( util.InclusionEnd) from #tmp2 union all select * from #tmp3 select ' TestInclusion' alt into #OutputQwerty -- ( util.InclusionEnd) exec util.InclusionEnd -- #Output util.InclusionBegin end go set nocount on set ansi_warnings off if object_id('tempdb..#ttInclusionParameters', 'U') is not null drop table #ttInclusionParameters go select ' 1: TestInclusion. myI : 2 3. 1 : " TestInclusion"' exec dbo.TestInclusion 2 go select ' 2: TestInclusion. testSum : 5' if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max)) exec util.InclusionRun ' select sum(myI) testSum from #InclusionOutput1 ', 1, '#InclusionOutput' exec dbo.TestInclusion 2 Result:
----------------------------------------------------------------------------------------------------------------------------------------------------------- 1: TestInclusion. myI : 2 3. 1 : " TestInclusion" myI ----------- 2 3 alt ----------------------- TestInclusion ------------------------------------------------------------------------------------------------------ 2: TestInclusion. testSum : 5 testSum ----------- 5 The functions themselves:
Hidden text
if not exists(select top 1 null from sys.schemas where name = 'util') begin exec ('create schema util') end go alter procedure util.InclusionBegin as begin /* : 1. 1.1. : if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin 1.1. select' #Output ( into #Output, into #Output5, into #OutputMySelect) 1.2. : exec util.InclusionEnd -- , #Output, util.InclusionBegin 2. , , ( #Output* ): if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int) exec util.InclusionRun('<sql >') . util.InclusionRun */ set nocount on set ansi_warnings off declare @lvl int if object_id('tempdb..#ttInclusionParameters', 'U') is not null begin select @lvl = max(lvl) from #ttInclusionParameters -- null , if (@lvl is not null) begin insert #ttInclusionParameters(lvl, pr) select @lvl+1 lvl, null pr end end if object_id('tempdb..#ttInclusion', 'U') is not null begin -- #Output, util.InclusionEnd insert #ttInclusion(lvl, i) select isnull(@lvl, 0), so.object_id i from tempdb.sys.objects so where so.type = 'U' and so.name like '#[^#]%' and object_id('tempdb..' + so.name, 'U') is not null and not exists (select top 1 null from #ttInclusion where i = so.object_id) end end GO go alter procedure util.InclusionEnd as begin /* : 1. 1.1. : if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int) exec util.InclusionBegin 1.1. select' #Output ( into #Output, into #Output5, into #OutputMySelect) 1.2. : exec util.InclusionEnd -- , #Output, util.InclusionBegin 2. , , ( #Output* ): if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int) exec util.InclusionRun('<sql >') . util.InclusionRun */ set nocount on set ansi_warnings off ---------------------------------------------------------------------------------------------------- -- declare @lvl int , @p0 varchar(max) --(@sql) sql , @p1 varchar(max) --(@notShowOutput) '1' , #Output, , @p2 varchar(max) --(@replaceableTableName) if object_id('tempdb..#ttInclusionParameters', 'U') is not null begin -- select @p1 = max(val) from #ttInclusionParameters where pr = 1 -- (max(lvl) - null util.InclusionBegin) select @lvl = max(lvl) - 1 from #ttInclusionParameters if @lvl is not null begin -- select @p0 = max(case when pr = 0 then val end) , @p2 = max(case when pr = 2 then val end) from #ttInclusionParameters where lvl = @lvl having max(pr) is not null -- , , null- delete #ttInclusionParameters where lvl >= @lvl and (lvl > @lvl or @p0 is not null) end end ---------------------------------------------------------------------------------------------------- -- #Output if object_id('tempdb..#InclusionOutputs', 'U') is not null drop table #InclusionOutputs create table #InclusionOutputs(i int, tableName varchar(max), num int) if object_id('tempdb..#ttInclusion', 'U') is not null begin insert #InclusionOutputs(i, tableName, num) select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num from tempdb.sys.objects so where so.type = 'U' and so.name like '#[^#]%' and object_id('tempdb..' + so.name, 'U') is not null and so.name like '#Output%' and not exists (select top 1 null from #ttInclusion where i = so.object_id and lvl <= isnull(@lvl, lvl)) -- , delete #ttInclusion where lvl <= @lvl end else begin insert #InclusionOutputs(i, tableName, num) select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num from tempdb.sys.objects so where so.type = 'U' and so.name like '#[^#]%' and object_id('tempdb..' + so.name, 'U') is not null and so.name like '#Output%' end ---------------------------------------------------------------------------------------------------- -- ( - #Output) declare @srcsql varchar(max) -- util.InclusionRun if (@p0 is not null and @p0 <> '') begin -- @replaceableTableName if (@p2 is not null and @p2 <> '') begin select @p0 = replace(@p0, @p2 + cast(num as varchar(10)), replace(tableName, '#', '#<tokenAfterReplace>')) from #InclusionOutputs order by num desc select @p0 = replace(@p0, '<tokenAfterReplace>', '') end -- select @srcsql = isnull(@srcsql + ' ' + char(13), '') + @p0 + ' ' + char(13) end -- #Output if (@p1 is null or @p1 <> '1') -- 1, ! begin -- select @srcsql = isnull(@srcsql + ' ' + char(13), '') -- select @srcsql = isnull(@srcsql + ' ', '') + 'select * from ' + tableName from #InclusionOutputs order by num asc end if (@srcsql is not null) begin exec (@srcsql) end end go alter procedure util.InclusionRun @sql varchar(max), --sql ( util.InclusionEnd) @notShowOutput bit, -- = 1, #Output @replaceableTableName varchar(100) = '#Output' -- @sql, #Output* . -- , #InclusionOutput, #Output55 #Output0A, -- @sql #Output55 #InclusionOutput1, #Output0A #InclusionOutput2 as begin set nocount on set ansi_warnings off if object_id('tempdb..#ttInclusionParameters', 'U') is null begin print ' util.InclusionRun , .. #ttInclusionParameters! ' return end declare @lvl int select @lvl = isnull(max(lvl), 0) + 1 from #ttInclusionParameters insert #ttInclusionParameters(lvl, pr, val) select @lvl, 0, @sql union all select @lvl, 1, '1' where @notShowOutput = 1 union all select @lvl, 2, @replaceableTableName end Other methods
You can use the parameter transfer from the function (
OUTPUT ) and, based on its value, restore the table. For example, you can pass a cursor or XML.There is an article on this topic.
I don’t see the point of using the cursor for this task, only if the cursor is initially required. But XML looks promising. Here are very interesting results of performance tests.
It is interesting to hear how you use ways to simplify this task :)
UPD 03/31/2014: Adjusted the post on the ideas from the comments
Source: https://habr.com/ru/post/217649/
All Articles