Shulou(Shulou.com)05/31 Report--

Sql server uses the common table expression CTE to automatically generate consecutive numbers and dates by writing general functions recursively. I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Question:

In database script development, it is sometimes necessary to generate a bunch of consecutive numbers or dates. For example, yearly report needs consecutive numbers to make a year, and daily report needs to generate a date for each day in a certain time range.

The built-in system table master..spt_values has some limitations, except that consecutive dates cannot be generated directly from 0 to 2047 (verification script: select * from master..spt_values b where b.type ='P').

Perhaps most people will come up with a stupid way to insert data into temporary tables one by one through the while loop, one number at a time or one day at a time, but the interaction with the database server is too frequent. If you generate 1W consecutive numbers, you have to interact with the database server 1W times, terrible! If there are 1000 clients that need to call this while loop, that's 1000W times! Terrible!

Solution:

The common table expression CTE can be implemented recursively and written as a general table-valued function to facilitate calling, encapsulating and simplifying the use, and returning tabular data.

CTE prepares the data in memory instead of going back and forth between the server and the client one at a time. If you need to insert into the temporary table, all the data is inserted at once.

If the incoming parameter is a number, a consecutive number is generated; if the passed-in parameter is a date, a consecutive date is generated. Don't you think it's convenient?

Function script:

If object_id ('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTablego/* function: consecutive strings (numbers or dates) are returned in table form by author: zhang502219048 2018-12-10 script Source: https://www.cnblogs.com/zhang502219048/p/11108991.html-example 1 (number): select * from dbo.fun_ConcatStringsToTable (1, 10000)-example 2 (numeric text): select * from dbo.fun_ConcatStringsToTable (' 1') Example 3 (date): declare @ dateBegin datetime = '2009-1-1-1), @ dateEnd datetime =' 2018-12-31 'select * from dbo.fun_ConcatStringsToTable (@ dateBegin, @ dateEnd)-- example 4 (date text): select * from dbo.fun_ConcatStringsToTable (' 2009-1-1-1, '2018-12-31') * * / create function [dbo]. [fun_ConcatStringsToTable] (@ strBegin as nvarchar) @ strEnd as nvarchar (100) returns @ tempResult table (vid nvarchar (100)) asbegin-- Digital if isnumeric (@ strBegin) = 1 and isnumeric (@ strEnd) = 1 begin-- Recursive batch insertion of digital data using CTE With cte_table (id) as (select cast (@ strBegin as int) union all select id + 1 from cte_table where id < @ strEnd) insert into @ tempResult select cast (id as nvarchar (100)) from cte_table option (maxrecursion 0) end-date else if isdate (@ strBegin) = 1 and isdate (@ strEnd) = 1 begin-- insert date data in batch using CTE recursion With cte_table (CreatedDate) as (select cast (@ strBegin as datetime) union all select dateadd (day, 1, CreatedDate) from cte_table where CreatedDate < @ strEnd) insert into @ tempResult select convert (varchar (10), CreatedDate, 120) from cte_table option (maxrecursion 0) end return;endgo

Example of calling a function:

-- example 1 (number): select * from dbo.fun_ConcatStringsToTable (1, 10000)-- example 2 (numeric text): select * from dbo.fun_ConcatStringsToTable ('1million,' 10000')-- example 3 (date): declare @ dateBegin datetime = '2009-1-1, @ dateEnd datetime =' 2018-12-31 'select * from dbo.fun_ConcatStringsToTable (@ dateBegin @ dateEnd)-example 4 (date text): select * from dbo.fun_ConcatStringsToTable ('2009-1-1-1,' 2018-12-31')

The result of running the script:

Conclusion:

As you can see from the above figures, the purpose of generating consecutive numbers and dates is achieved by simply calling fun_ConcatStringsToTable, a custom table-valued function, and specifying a start and end number or date.

Extend:

What if you want to generate consecutive months? The blogger has also written a script for you here, and you can make a table-valued function on this basis if necessary:

With cte_table (CreatedDate) as (select cast ('2017-12-1' as datetime) union all select dateadd (month, 1, CreatedDate) from cte_table where CreatedDate < '2018-04-01') select convert (varchar (7), CreatedDate, 120) as YearMonthfrom cte_tableoption (maxrecursion 0) after reading the above, have you mastered the method that sql server uses the common table expression CTE to automatically generate consecutive numbers and dates by writing general functions recursively? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.