ファイル名を動的に生成する
SQL Server を使用して可変ファイル名を動的に作成し、それをテーブルにバインドして、実際の例でデータを使用する方法
Context
In the process of building an automated export process for one of our clients we came across the need to generate file names on the fly. To do this we knew that it would need to be a hard coded function as you can't use variables within DATEPART, but it was still something that we wanted to make as easy as possible to use. Allowing the client to put variables in the files names would be the way forward.
To start with, add the following two functions. We've popped some links in to give you more information on how they work.
Pre-Requisites
ALTER FUNCTION [dbo].[TextPad](@PadChar CHAR(1),@PadValue NVARCHAR(100),@PadLen INT) RETURNS NVARCHAR(100) WITH SCHEMABINDING AS BEGINRETURN ISNULL(REPLICATE(@PadChar,@PadLen-LEN(@PadValue))+@PadValue,LEFT(@PadValue,@PadLen))ENDGOALTER FUNCTION [App].[TextToRows](@Delim NVARCHAR(10),@Value NVARCHAR(MAX))--Delimeter and Search StringRETURNS @Table TABLE(WordInt BIGINT IDENTITY(1,1) PRIMARY KEY,WordStr NVARCHAR(MAX)) WITH SCHEMABINDING --Return TableAS BEGIN--IF @Trim=1 SET @Value=LTRIM(RTRIM(@Value))DECLARE @CurPos BIGINT=0DECLARE @NextPos BIGINT=CHARINDEX(@Delim,@Value,@CurPos+1+LEN(@Delim))WHILE @NextPos>0 BEGININSERT INTO @Table(WordStr) SELECT SUBSTRING(@Value,@CurPos+1,(@NextPos-@CurPos)-1)SET @CurPos=@NextPosSET @NextPos=CHARINDEX(@Delim,@Value,@CurPos+1+LEN(@Delim))ENDINSERT INTO @Table(WordStr) SELECT RIGHT(@Value,LEN(@Value)-@CurPos)RETURNENDGOText to Rows Function
Text Padding Function
Dynamic File Name Function
Now that we have the basics in, we can move to the next stage. Those of you who are eagle eyed may have noticed the SCHEMABINDING part in the statement. As the eventual aim is to have this set as a column, every child function will need schema binding turned on.
It's a fairly simple function that uses a set of replaces, but in an order from longest to shortest. Text replacement candidates are worked out by two text two rows functions that split the text up into new rows.
Where we have put the date in hardcoded for testing, you can use GETDATE() for the machine date, or GETUTCDATE() for Universal Date
SQL
ALTER FUNCTION UpdFileName(@N NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) WITH SCHEMABINDING AS BEGINDECLARE @D DATETIME='2021-06-19 15:00:00',@R NVARCHAR(MAX)=''(SELECT @R=@R+(CASE WHEN y.WordInt=1 AND x.WordInt>1 THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( y.WordStr, 'yyyy',[dbo].[TextPad]('0',DATEPART(YEAR,@D),4) COLLATE latin1_general_cs_as), 'dd',[dbo].[TextPad]('0',DATEPART(DAY,@D),2) COLLATE latin1_general_cs_as), 'MM',[dbo].[TextPad]('0',DATEPART(MONTH,@D),2) COLLATE latin1_general_cs_as), 'mm',[dbo].[TextPad]('0',DATEPART(MINUTE,@D),2) COLLATE latin1_general_cs_as), 'hh',[dbo].[TextPad]('0',(CASE WHEN DATEPART(HOUR,@D)>12 THEN DATEPART(HOUR,@D)-12 ELSE DATEPART(HOUR,@D) END),2) COLLATE latin1_general_cs_as), 'HH',[dbo].[TextPad]('0',DATEPART(HOUR,@D),2) COLLATE latin1_general_cs_as), 'ss',[dbo].[TextPad]('0',DATEPART(SECOND,@D),2) COLLATE latin1_general_cs_as), 'qq',[dbo].[TextPad]('0',DATEPART(QUARTER,@D),2) COLLATE latin1_general_cs_as), 'wk',[dbo].[TextPad]('0',DATEPART(WEEK,@D),2) COLLATE latin1_general_cs_as), 'yy',RIGHT(CONVERT(NVARCHAR(10),DATEPART(YEAR,@D)),2) COLLATE latin1_general_cs_as), 'ms',CONVERT(NVARCHAR(10),DATEPART(MILLISECOND,@D)) COLLATE latin1_general_cs_as), 'dy',CONVERT(NVARCHAR(10),DATEPART(DAYOFYEAR,@D)) COLLATE latin1_general_cs_as), 'dw',CONVERT(NVARCHAR(10),DATEPART(WEEKDAY,@D)) COLLATE latin1_general_cs_as), 's',CONVERT(NVARCHAR(10),DATEPART(SECOND,@D)) COLLATE latin1_general_cs_as), 'h',CONVERT(NVARCHAR(10),(CASE WHEN DATEPART(HOUR,@D)>12 THEN DATEPART(HOUR,@D)-12 ELSE DATEPART(HOUR,@D) END)) COLLATE latin1_general_cs_as), 'H',CONVERT(NVARCHAR(10),DATEPART(HOUR,@D)) COLLATE latin1_general_cs_as), 'M',CONVERT(NVARCHAR(10),DATEPART(MONTH,@D)) COLLATE latin1_general_cs_as), 'm',CONVERT(NVARCHAR(10),DATEPART(MONTH,@D)) COLLATE latin1_general_cs_as), 'w',CONVERT(NVARCHAR(10),DATEPART(WEEKDAY,@D)) COLLATE latin1_general_cs_as), 'd',CONVERT(NVARCHAR(10),DATEPART(DAY,@D),2) COLLATE latin1_general_cs_as), 'n',CONVERT(NVARCHAR(10),DATEPART(MINUTE,@D),2) COLLATE latin1_general_cs_as), 'y',CONVERT(NVARCHAR(10),DATEPART(DAYOFYEAR,@D)) COLLATE latin1_general_cs_as) ELSE y.WordStr END) FROM App.TextToRows('{',@N) xOUTER APPLY App.TextToRows('}',WordStr) y)RETURN @RENDGOBind to a table
The final stage is binding it to a table. To do that simply declare the column name and then use an AS statement and the function name with any variables required. We've popped some demo output below too.
SQL
CREATE TABLE FN(FN NVARCHAR(MAX),FP AS dbo.UpdFileName(FN))GOINSERT INTO FN SELECT 'TestFile{dd_MM_yyyy_hh_mm_ss}.csv'INSERT INTO FN SELECT 'TestFile{dd_MM_yyyy_HH_mm_ss}.csv'INSERT INTO FN SELECT 'TestFile_{qq dMyy h}.txt'INSERT INTO FN SELECT 'TestFile_{qq dMyy H}.txt'SELECT * FROM FN