All we need is an easy explanation of the problem, so here it is.
I have several hundred tables in a database that have the same structure:
SomeId, Pos, Varying Number of Other Fields
So, for example, one table may look like this:
PersonId, Pos, Hobby, Degree
12345, 1, Basketball, Bachelor of Science
12345, 2, Baseball, Master of Science
12345, 3, Boxing, NULL
12345, 4, Tennis, NULL
22222, 1, Golf, Bachelor of Science
22222, 2, NULL, Master of Science
22222, 3, NULL, Doctorate
I want to roll up the values for every column 3-N. So this would become:
12345, "Basketball, Baseball, Boxing, Tennis", "Bachelor of Science, Master of Science"
22222, "Golf", "Bachelor of Science, Master of Science, Doctorate"
Another table might look like this:
ClientId, Pos, Location, Language, CommunicationType
33333, 1, North Carolina, English, Phone
33333, 2, New York, Spanish, Email
33333, 3, NULL, Portuguese, NULL
44444, 1, California, English, Phone
44444, 2, NULL, NULL, Email
and become this:
33333, "North Carolina, New York", "English, Spanish, Portugeue", "Phone, Email"
44444, "California", "English", "Phone, Email"
What I would like to do is create a TVF where I can specify the table name and have the function return its fields. Ideally, rolled up like I just demonstrated above.
Solomon Rutzky provided a solution (SQL Server: Pass table name into table valued function as a parameter) where he showed how to use XML and CASE statements to accept table names in a TVF.
Here’s an adaptation:
DECLARE @TableName sysname = 'objects'
/*
DECLARE @TableName sysname = 'columns'
DECLARE @TableName sysname = 'indexes'
*/
SELECT tab.BaseData.value(N'/row[1]/@name', N'VARCHAR(128)') AS [name],
tab.BaseData.value(N'/row[1]/@object_id', N'BIGINT') AS [object_id],
*
FROM (
SELECT CASE @TableName
WHEN N'objects' THEN (SELECT * FROM master.sys.tables FOR XML RAW, TYPE)
WHEN N'indexes' THEN (SELECT * FROM master.sys.indexes FOR XML RAW, TYPE)
WHEN N'columns' THEN (SELECT * FROM master.sys.columns FOR XML RAW, TYPE)
END AS [BaseData]
) tab;
If I were to create a monster CASE statement and account for all possible incoming table names, is there a way to reference the columns by ordinal position (rather than name like I’m doing above)? Even better, roll up and delimit their values too (which is my ultimate goal)?
Thank you in advance!
Also, here’s the DDL to create my two sample tables:
CREATE TABLE [dbo].[Person](
[PersonId] [int] NULL,
[Pos] [int] NULL,
[Hobby] [varchar](100) NULL,
[Degree] [varchar](50) NULL
)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 1, N'Basketball', N'Bachelor of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 2, N'Baseball', N'Master of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 3, N'Boxing', NULL)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (12345, 4, N'Tennis', NULL)
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 1, N'Golf', N'Bachelor of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 2, NULL, N'Master of Science')
GO
INSERT [dbo].[Person] ([PersonId], [Pos], [Hobby], [Degree]) VALUES (22222, 3, NULL, N'Doctorate')
GO
CREATE TABLE [dbo].[Client](
[ClientId] [int] NULL,
[Pos] [int] NULL,
[Location] [varchar](100) NULL,
[Language] [varchar](50) NULL,
[CommunicationType] [varchar](50) NULL
)
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 1, N'North Carolina', N'English', N'Phone')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 2, N'New York', N'Spanish', N'Email')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (33333, 3, NULL, N'Portuguese', NULL)
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (44444, 1, N'California', N'English', N'Phone')
GO
INSERT [dbo].[Client] ([ClientId], [Pos], [Location], [Language], [CommunicationType]) VALUES (44444, 2, NULL, NULL, N'Email')
GO
SELECT * FROM Person;
SELECT * FROM Client;
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
is there a way to reference the columns by ordinal position
Yes there is but I’m not sure how that would help you doing what you want. You put the ordinal position in the predicate, as you already do for row[1].
Changing '/row[1]/@name' to instead get the third column would look like '/row[1]/@*[3]'. You should be aware of that null values does not create any attributes so your data in the third attribute will not always come from the third column.
To fix that you could generate elements instead of attributes for column values and use XSINIL to get empty elements for null values in columns, ex: SELECT * FROM master.sys.indexes FOR XML RAW, ELEMENTS XSINIL, TYPE. Then you need to select the third element from the XML instead of the third attribute '/row[1]/*[3]'.
You are already on a path to "create a monster CASE statement and account for all possible incoming table names" so why not create a monster query that does what you want instead, without the XML stuff.
select T.PersonId as Id,
'"' + string_agg(T.Hobby, ',') within group (order by T.Pos) + '", ' +
'"' + string_agg(T.Degree, ',') within group (order by T.Pos) + '"' as Value
from dbo.Person as T
where @TableName = N'Person'
group by T.PersonId
union all
select T.ClientId,
'"' + string_agg(T.Location, ',') within group (order by T.Pos) + '", ' +
'"' + string_agg(T.Language, ',') within group (order by T.Pos) + '", ' +
'"' + string_agg(T.CommunicationType, ',') within group (order by T.Pos) + '"'
from dbo.Client as T
where @TableName = N'Client'
group by T.ClientId;
You could use dynamic SQL against meta tables to generate the above query if you are in a situation where you need to update the function often or even automatically.
Since you are on SQL Server 2016 you don’t have string_agg() you need to use for xml path to do the concatenation. The query got bigger but it is the same principle and can still be created using dynamic SQL.
select T.PersonId as Id,
'"' + stuff((
select ', '+T2.Hobby
from dbo.Person as T2
where T.PersonId = T2.PersonId
order by T2.Pos
for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' +
'"' + stuff((
select ', '+T2.Degree
from dbo.Person as T2
where T.PersonId = T2.PersonId
order by T2.Pos
for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '"' as Value
from dbo.Person as T
where @TableName = N'Person'
group by T.PersonId
union all
select T.ClientId,
'"' + stuff((
select ', '+T2.Location
from dbo.Client as T2
where T.ClientId = T2.ClientId
order by T2.Pos
for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' +
'"' + stuff((
select ', '+T2.Language
from dbo.Client as T2
where T.ClientId = T2.ClientId
order by T2.Pos
for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '", ' +
'"' + stuff((
select ', '+T2.CommunicationType
from dbo.Client as T2
where T.ClientId = T2.ClientId
order by T2.Pos
for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') + '"'
from dbo.Client as T
where @TableName = N'Client'
group by T.ClientId;
Method 2
You cannot use dynamic SQL here, as that will not work inside a TVF. You could use dynamic to generate the actual code below though.
Given that you are on SQL Server 2016, you do not have STRING_AGG available, so you are going to have to use FOR XML/STUFF method, which is pretty complex with multiple columns.
It’s not necessary or performant to keep querying the data again for every column, you can use a combination of APPLY and .value
CREATE OR ALTER FUNCTION GetTableInfo (@Tablename sysname)
RETURNS TABLE
AS RETURN
(
SELECT PersonId AS Id,
'"' + STUFF(v.XmlValues.query('for $c in col1 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
'"' + STUFF(v.XmlValues.query('for $c in col2 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
'"' + STUFF(v.XmlValues.query('for $c in col3 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' AS RollupValues
FROM (SELECT DISTINCT PersonId FROM Person) t1
CROSS APPLY (VALUES((
SELECT col1, col2, col3
FROM Person t2
WHERE t2.PersonId = t1.PersonId
ORDER BY t2.Pos
FOR XML PATH (''), TYPE
))) v(XmlValues)
WHERE @Tablename = 'Person'
UNION ALL
SELECT ClientId,
'"' + STUFF(v.XmlValues.query('for $c in col1 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
'"' + STUFF(v.XmlValues.query('for $c in col2 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' +
'"' + STUFF(v.XmlValues.query('for $c in col3 return concat(",", string($c))').value('text()[1]','nvarchar(max)'),1,1,'') + '" ' AS RollupValues
FROM (SELECT DISTINCT ClientId FROM Client) t1
CROSS APPLY (VALUES((
SELECT col1, col2, col3
FROM Client t2
WHERE t2.ClientId = t1.ClientId
ORDER BY t2.Pos
FOR XML PATH (''), TYPE
))) v(XmlValues)
WHERE @Tablename = 'Client'
UNION ALL
......
);
GO
To reiterate, you need to replace col1,col2 with the actual column names, same with the table names. You cannot do this with dynamic SQL in a function.
For completeness, I will show the STRING_AGG method which is far simpler:
CREATE OR ALTER FUNCTION GetTableInfo (@Tablename sysname)
RETURNS TABLE
AS RETURN
(
SELECT PersonId AS Id,
'"' + STRING_AGG(CAST(col1 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
'"' + STRING_AGG(CAST(col2 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
'"' + STRING_AGG(CAST(col3 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' AS RollupValues
FROM Person
WHERE @Tablename = 'Person'
GROUP BY PersonId
UNION ALL
SELECT ClientId,
'"' + STRING_AGG(CAST(col1 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
'"' + STRING_AGG(CAST(col2 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' +
'"' + STRING_AGG(CAST(col3 AS nvarchar(max)), ', ') WITHIN GROUP (ORDER BY Pos) + '" ' AS RollupValues
FROM Client
WHERE @Tablename = 'Client'
GROUP BY ClientId
UNION ALL
......
);
GO
Method 3
I don’t believe it is possible to do exactly what you want with a function, because it must have a fixed output shape (number, types, and names of columns).
One possible approximation is to return a fixed number of columns (with generic names), each containing an aggregation of strings, with null returned for the extra columns not applicable to a source table with fewer columns than the maximum.
As noted in other answers, STRING_AGG is ideal for that, but not available in SQL Server 2016. An efficient replacement for that can be provided by a SQL CLR streaming table-valued function as noted in the linked Q & A. Now, I know you’ll say you cannot use SQL CLR for whatever reason, but for the benefit of future readers with a similar requirement, here is an example implementation.
The code uses a loopback connection for technical reasons, so the first couple of parameters specify the server/instance name and the database name. The third parameter is the table, which is expected to contain an integer id column, and an integer ordering column in the second position. The remaining columns are all assumed to be strings.
This sample implementation is limited to five such columns. It makes a single ordered pass over the table, and minimizes memory usage by only keeping the current group in memory at one time. It ought to be much faster than the XML PATH solutions.
Example calls and results
SELECT
GTD.id,
GTD.col1,
GTD.col2,
GTD.col3,
GTD.col4,
GTD.col5
FROM dbo.GetTableData
(
@@SERVERNAME,
DB_NAME(),
N'dbo.Person'
) AS GTD;
| id | col1 | col2 | col3 | col4 | col5 |
|---|---|---|---|---|---|
| 12345 | Basketball, Baseball, Boxing, Tennis | Bachelor of Science, Master of Science | NULL | NULL | NULL |
| 22222 | Golf | Bachelor of Science, Master of Science, Doctorate | NULL | NULL | NULL |
SELECT
GTD.id,
GTD.col1,
GTD.col2,
GTD.col3,
GTD.col4,
GTD.col5
FROM dbo.GetTableData
(
@@SERVERNAME,
DB_NAME(),
N'dbo.Client'
) AS GTD;
| id | col1 | col2 | col3 | col4 | col5 |
|---|---|---|---|---|---|
| 33333 | North Carolina, New York | English, Spanish, Portuguese | Phone, Email | NULL | NULL |
| 44444 | California | English | Phone, Email | NULL | NULL |
T-SQL
CREATE ASSEMBLY [DBA] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E8B986600000000000000000E00022200B013000001C00000006000000000000123A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000C03900004F00000000400000A802000000000000000000000000000000000000006000000C000000883800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000181A000000200000001C000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000001E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002200000000000000000000000000004000004200000000000000000000000000000000F439000000000000480000000200050018260000701200000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000761FFE730400000625027D0500000425037D0700000425047D090000042A000013300300710000000100001102A50300001B0A03067B1000000A810D00000104067B1100000A16A30E000001810E00000105067B1100000A17A30E000001810E0000010E04067B1100000A18A30E000001810E0000010E05067B1100000A19A30E000001810E0000010E06067B1100000A1AA30E000001810E0000012A1E02281200000A2A6602281200000A02037D0100000402281300000A7D030000042A001B3002003400000002000011027B010000040A061FFC5917360606175917351F00061FFC2E08061759173602DE1100DE0E022808000006DC022807000006DC2A011C000002002300022500070000000002001500172C0007000000001B300500BD03000003000011027B010000040B074503000000070000008B02000083030000160ADD9B03000002157D01000004731400000A25027B040000046F1500000A25027B060000046F1600000A25176F1700000A251B6F1800000A25166F1900000A0C02086F1A00000A731B00000A7D0A000004021FFD7D01000004027B0A0000046F1C00000ADE0F130572010000701105731D00000A7A027239000070027B08000004725F000070281E00000A027B0A000004731F00000A7D0B000004021FFC7D01000004027B0B0000046F2000000A0D7E2100000A096F2200000A2C3B1B8D1F00000125167271000070A22517027B08000004A225187281000070A22519027B06000004A2251A72B3000070A2282300000A732400000A7A027B0B00000472B7000070027B0800000472D5000070281E00000A6F2500000A02027B0B0000046F2600000A7D0C00000402027B0C0000046F2700000A18597D0D000004027B0D0000041B312C72F50000707209010070027B08000004027B0D0000048C240000011B8C24000001282800000A732900000A7A02027B0D0000048D1A0000017D0E000004021B8D0E0000017D0F0000041613062B35027B0F00000411067E2A00000AA40E0000011106027B0D0000042F13027B0E00000411062000010000732B00000AA211061758130611061B32C67E2C00000A1304384201000002027B0C000004166F2D00000A7D10000004027C10000004282E00000A3A14010000027C1000000411048C0D000001FE160D0000016F2200000A3A8D0000001204282E00000A2D721613072B35027B0F0000041107027B0E00000411079A6F2F00000A733000000AA40E000001027B0E00000411079A6F3100000A261107175813071107027B0D00000432C1021104027B0F000004733200000A8C0300001B7D0200000402177D01000004170ADD17010000021FFC7D010000041204027C10000004283300000A283400000A1613082B5B027B0C000004110818586F3500000A13091209283600000A2D3B027B0E00000411089A6F3700000A173114027B0E00000411089A727D0100706F3800000A26027B0E00000411089A1209283900000A6F3800000A261108175813081108027B0D000004329B027C10000004FE150D000001027B0C0000046F3A00000A3AAEFEFFFF16130A2B26027B0F000004110A027B0E000004110A9A6F2F00000A733000000AA40E000001110A1758130A110A027B0D00000432D0021104027B0F000004733200000A8C0300001B7D0200000402187D01000004170ADE1F021FFC7D01000004160A022808000006022807000006DE07022805000006DC062A0000004134000000000000730000000D000000800000000F000000140000010400000000000000B4030000B403000007000000000000006E02157D01000004027B0A0000042C0B027B0A0000046F0800000A2A72021FFD7D01000004027B0B0000042C0B027B0B0000046F0800000A2A1E027B020000042A1A733B00000A7A133002004F00000006000011027B010000041FFE3318027B03000004281300000A330B02167D01000004020A2B071673040000060A06027B050000047D0400000406027B070000047D0600000406027B090000047D08000004062A1E02280C0000062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000044050000237E0000B00500003007000023537472696E677300000000E00C00008401000023555300640E0000100000002347554944000000740E0000FC03000023426C6F6200000000000000020000015717A20B0902000000FA013300160000010000002600000003000000100000000D0000000B000000050000003B000000100000000600000001000000020000000200000007000000030000000100000002000000010000000000420301000000000006009F0203050600D102030506004002F0040F00230500000600840562030600FA0162030600540203050A008A0252040A008F0152040A00890152040600C1014E050A00BF0252040A004C0032050A00FC0232050600640062030600250203050600CD01620306006D044E050A003704B0050600C3036203060022001A01060014001A010A007B03B0050A007701B0050A000104B00506004404EF0606007202F0040600C60562030A001D04CD030A006E03CD0306001D0362030A006D01CD030600C00662030600BB0362030A00F403CD0306004F00620306009F03620306008903620300000000EF0000000000010001000100100061050000150001000100030110000100000015000100040001001A02E7000100A406980001005001E7000100A301E40006009E01E4000100ED01E4000600E801E4000100DE01E4000600D901E40001005500F80001008A00FC0001009D0000010100AF00E7000100C20004010100CB0009010100E600C100502000000000960004010E01010070200000000096000B0716010400ED20000000008618EA0406000B00F520000000008618EA0401000B00102100000000E101FF0106000C006C2100000000E101E60616000C006C250000000081003B0006000C0088250000000081007C0006000C00A52500000000E109370625000C00AD2500000000E1018B0506000C00A52500000000E109790625000C00B42500000000E10179042D010C000F2600000000E101BD0438000C0000000100A30100000200ED0100000300DE01000001001307020002006A0102000300300002000400710002000500980002000600AA0002000700BD00000001001A0203000A0003002D000300060003004500030049000900EA0401001100EA0406001900EA040A003900EA0410004100EA0406006100EA0406008100EA0406008900120206009100E60616000C00980620009100AA0506009100980625001400DC042F005900DC043800D900EA0406001C00350053001C00760057002900EA040600E10035015B009900EA0406009900B2017500990024037500990017077A009900D30601009900C8067A00E90006037F00B900EA047500F10069030600A100EA048300F90076058A00C100EA0491000101E6032500090150039800290047059B00F9007605A0001101EA0475000101FB067500C1000F04A6001901B106AB00F9007D05AF002901EA04B70071005D03BD00D100EA04010069005D03C100C9004900C50069005703160029001B037F007100EA047500D100E003CB001C00EA04D0006900EF02AB006900EA040100C900F902D800710057031600D1003703AB00D1008201DE007100EF027F001901650116003101EA040600200023006A0120002B00970124003300D5032E000B0039012E00130042012E001B00610144003300D50363003B00D00364003300E80380007B00D003A0007B00D00320017B00D00340017B00D00360017B00D00380017B00D003A0017B00D0033D005F006300E400E700EA00030001000000D205350100001006350102000900030002000B00050003000A00110003000C001300030012001500030014001700030016001900030018001B0003001A001D001A0029004900048000000000000000000000000000000000DC000000040000000000000000000000EF00110100000000040000000000000000000000EF00F800000000000300020000000000003C4765745461626C65446174613E645F5F300049456E756D657261626C6560310049456E756D657261746F72603100636F6C31004974656D31003C3E6D5F5F46696E616C6C79310047657453716C496E743332003C6C6F6F706261636B3E355F5F320056616C75655475706C65603200636F6C32004974656D32003C3E6D5F5F46696E616C6C7932003C636F6D6D616E643E355F5F3300636F6C33003C7265616465723E355F5F3400636F6C34003C636F6C756D6E733E355F5F3500636F6C35003C73623E355F5F36003C53716C537472696E67733E355F5F3700446174616261736537003C69643E355F5F38003C4D6F64756C653E0053797374656D2E44617461004765745461626C6544617461006D73636F726C69620053797374656D2E436F6C6C656374696F6E732E47656E65726963006765745F43757272656E744D616E616765645468726561644964003C3E6C5F5F696E697469616C54687265616449640052656164006964004462436F6D6D616E640053716C436F6D6D616E6400417070656E640053797374656D446174614163636573734B696E64003C3E335F5F536572766572496E7374616E6365007365745F44617461536F757263650049456E756D657261626C650049446973706F7361626C65003C3E335F5F5461626C654E616D65003C3E335F5F44617461626173654E616D6500547970650053797374656D2E49446973706F7361626C652E446973706F7365003C3E315F5F737461746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C65417474726962757465004974657261746F7253746174654D616368696E6541747472696275746500446562756767657248696464656E4174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650053716C46616365744174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650047657453716C537472696E67006765745F436F6E6E656374696F6E537472696E6700546F537472696E67007365745F496E697469616C436174616C6F67006765745F4C656E677468004461746162617365372E646C6C0044424E756C6C006765745F49734E756C6C0053797374656D004F70656E004462436F6E6E656374696F6E0053716C436F6E6E656374696F6E004E6F74537570706F72746564457863657074696F6E00417267756D656E744F75744F6652616E6765457863657074696F6E00417267756D656E74457863657074696F6E0053797374656D2E446174612E436F6D6D6F6E00436C65617200457865637574655363616C6172004462446174615265616465720053716C446174615265616465720045786563757465526561646572004462436F6E6E656374696F6E537472696E674275696C6465720053716C436F6E6E656374696F6E537472696E674275696C646572004D6963726F736F66742E53716C5365727665722E5365727665720049456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261626C653C53797374656D2E4F626A6563743E2E476574456E756D657261746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C547970657300457175616C730053797374656D2E436F6C6C656374696F6E730055736572446566696E656446756E6374696F6E7300436F6E63617400466F726D6174004F626A6563740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E52657365740053797374656D2E446174612E53716C436C69656E7400456E7669726F6E6D656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E4F626A6563743E2E6765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74003C3E325F5F63757272656E74006765745F4669656C64436F756E7400436F6E76657274007365745F456E6C697374007365745F436F6E6E65637454696D656F7574004D6F76654E6578740053797374656D2E54657874007365745F436F6D6D616E64546578740046696C6C526F7700726F77007365745F496E7465677261746564536563757269747900000000374C006F006F0070006200610063006B00200063006F006E006E0065006300740069006F006E0020006600610069006C00650064002E000025530045004C0045004300540020004F0042004A004500430054005F004900440028002700011127002C00200027005500270029003B00010F5400610062006C006500200027000131270020006E006F007400200066006F0075006E006400200069006E00200064006100740061006200610073006500200001032E00001D530045004C0045004300540020002A002000460052004F004D002000001F20004F005200440045005200200042005900200031002C00200032003B0000135400610062006C0065004E0061006D00650000735400610062006C00650020007B0030007D00200068006100730020007B0031007D0020006100640064006900740069006F006E0061006C00200063006F006C0075006D006E0073002E00200054006800650020006D006100780069006D0075006D0020006900730020007B0032007D002E0000052C00200000002361A2CFA2AF2F4BB45B977B78FEFDCA000420010108032000010520010111110520010112190320000205151255011C04200013000320001C05151259011C08200015125501130004200012490B070115113D0211351D11390915113D0211351D11390306130003061301030000080307010811070B0208124D1C11351251080808113908042001010E04200101020320000E062002010E12510600030E0E0E0E062002010E125D02061C042001021C0500010E1D0E0420001265032000080700040E0E1C1C1C052002010E0E03061139030611350520011135080420001269072002011300130105200111390805200112690E02060E020608040701120C08B77A5C561934E0890306125D030612610306126504061D126904061D1139070003122D0E0E0E160007011C101135101139101139101139101139101139072000151255011C0328001C0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000002C01002755736572446566696E656446756E6374696F6E732B3C4765745461626C65446174613E645F5F3000008237010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730100000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E80E10D0A202020202020202020202020696420696E7465676572204E554C4C2C0D0A202020202020202020202020636F6C31206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C32206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C33206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C34206E76617263686172286D617829204E554C4C2C0D0A202020202020202020202020636F6C35206E76617263686172286D617829204E554C4C0D0A20202020040100000012010001005408074D617853697A658000000012010001005408074D617853697A65010100000000000000E8B9866000000000020000001C010000A4380000A41A0000525344533A0A7AF47A862C41B751DA4E601A730601000000433A5C55736572735C7061756C775C4F6E6544726976655C446F63756D656E74735C736F757263655C7265706F735C4461746162617365375C4461746162617365375C6F626A5C52656C656173655C4461746162617365372E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E83900000000000000000000023A0000002000000000000000000000000000000000000000000000F4390000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650037002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650037002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000143A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE OR ALTER FUNCTION dbo.GetTableData
(
@ServerInstance [nvarchar](128),
@DatabaseName [nvarchar](128),
@TableName [nvarchar](257)
)
RETURNS TABLE
(
id integer NULL,
col1 nvarchar(max) NULL,
col2 nvarchar(max) NULL,
col3 nvarchar(max) NULL,
col4 nvarchar(max) NULL,
col5 nvarchar(max) NULL
)
ORDER (id)
AS EXTERNAL NAME [DBA].[UserDefinedFunctions].[GetTableData];
C# Source
using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
public partial class UserDefinedFunctions
{
[SqlFunction(
DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.None,
FillRowMethodName = "FillRow",
TableDefinition = @"
id integer NULL,
col1 nvarchar(max) NULL,
col2 nvarchar(max) NULL,
col3 nvarchar(max) NULL,
col4 nvarchar(max) NULL,
col5 nvarchar(max) NULL
")]
public static IEnumerable GetTableData
(
[SqlFacet(MaxSize = 128)] string ServerInstance,
[SqlFacet(MaxSize = 128)] string DatabaseName,
[SqlFacet(MaxSize = 257)] string TableName
)
{
const string COMMA_SPACE = ", ";
const int MAX_OUTPUT_COLS = 5;
const int FIXED_COLS = 2;
// Establish loopback connection
var csb = new SqlConnectionStringBuilder
{
DataSource = ServerInstance,
InitialCatalog = DatabaseName,
IntegratedSecurity = true,
ConnectTimeout = 5,
Enlist = false
};
using var loopback = new SqlConnection(csb.ConnectionString);
try
{
loopback.Open();
}
catch (Exception e)
{
throw new Exception("Loopback connection failed.", e);
}
// Check supplied table name exists and is accessible
using var command = new SqlCommand([email protected]"SELECT OBJECT_ID('{TableName}', 'U');", loopback);
object object_id = command.ExecuteScalar();
if (Convert.DBNull.Equals(object_id))
{
throw new ArgumentException([email protected]"Table '{TableName}' not found in database {DatabaseName}.");
}
// Read table in the required order
command.CommandText = [email protected]"SELECT * FROM {TableName} ORDER BY 1, 2;";
SqlDataReader reader = command.ExecuteReader();
// Number of columns to process (skipping id and position)
int columns = reader.FieldCount - FIXED_COLS;
if (columns > MAX_OUTPUT_COLS)
{
throw new ArgumentOutOfRangeException(
nameof(TableName),
[email protected]"Table {TableName} has {columns} additional columns. The maximum is {MAX_OUTPUT_COLS}.");
}
// Create a string builder and SqlString array element for each output column
StringBuilder[] sb = new StringBuilder[columns];
SqlString[] SqlStrings = new SqlString[MAX_OUTPUT_COLS];
for (int i = 0; i < MAX_OUTPUT_COLS; i++)
{
SqlStrings[i] = SqlString.Null;
if (i < columns)
{
sb[i] = new StringBuilder(256);
}
}
// Remember the current id value
SqlInt32 previous_id = SqlInt32.Null;
// Process each row
while (reader.Read())
{
SqlInt32 id = reader.GetSqlInt32(0);
if (!id.IsNull)
{
if (!id.Equals(previous_id))
{
if (!previous_id.IsNull)
{
// Completed a group
for (int i = 0; i < columns; i++)
{
SqlStrings[i] = new SqlString(sb[i].ToString());
sb[i].Clear();
}
// Output the completed group
yield return (previous_id, SqlStrings);
}
previous_id = new SqlInt32(id.Value);
}
// Add the current row to the string builders
for (int i = 0; i < columns; i++)
{
var s = reader.GetSqlString(i + FIXED_COLS);
if (!s.IsNull)
{
if (sb[i].Length > 1)
{
sb[i].Append(COMMA_SPACE);
}
sb[i].Append(s.Value);
}
}
}
}
// Final group
for (int i = 0; i < columns; i++)
{
SqlStrings[i] = new SqlString(sb[i].ToString());
}
// Output the completed group
yield return (previous_id, SqlStrings);
}
// Called by SQL Server to populate each row returned from the TVF
public static void FillRow
(
object row,
out SqlInt32 id,
out SqlString col1,
out SqlString col2,
out SqlString col3,
out SqlString col4,
out SqlString col5
)
{
(SqlInt32 id, SqlString[] cols) v = ((SqlInt32, SqlString[]))row;
id = v.id;
col1 = v.cols[0];
col2 = v.cols[1];
col3 = v.cols[2];
col4 = v.cols[3];
col5 = v.cols[4];
}
}
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0