Skip to content

Commit aa040ef

Browse files
JeremyCaneyJeremyCaney
committed
Refactored GetAttributes as user-defined, table-valued function
This doesn't impact backward compatibility, in that the function can still be called as a stored procedure. But it provides more flexibility in how the code is integrated with queries. For instance, it can now be used in a `CROSS APPLY` to retrieve attributes from `TopicID`s in another query.
1 parent c6b50f3 commit aa040ef

File tree

2 files changed

+43
-23
lines changed

2 files changed

+43
-23
lines changed

OnTopic.Data.Sql.Database/Stored Procedures/GetAttributes.sql renamed to OnTopic.Data.Sql.Database/Functions/GetAttributes.sql

Lines changed: 42 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1,45 +1,65 @@
11
--------------------------------------------------------------------------------------------------------------------------------
2-
-- GET TOPIC ATTRIBUTES
2+
-- GET ATTRIBUTES
33
--------------------------------------------------------------------------------------------------------------------------------
44
-- Returns the most recent value of each attribute associated with a particular topic.
55
--------------------------------------------------------------------------------------------------------------------------------
6-
CREATE PROCEDURE [dbo].[GetAttributes]
6+
7+
CREATE
8+
FUNCTION [dbo].[GetAttributes] (
79
@TopicID INT = -1
10+
)
11+
RETURNS @Attributes TABLE
12+
(
13+
AttributeKey NVARCHAR(255) NOT NULL,
14+
AttributeValue NVARCHAR(MAX) NOT NULL,
15+
IsExtendedAttribute BIT,
16+
Version DATETIME
17+
)
818
AS
919

10-
--------------------------------------------------------------------------------------------------------------------------------
11-
-- DECLARE AND SET VARIABLES
12-
--------------------------------------------------------------------------------------------------------------------------------
13-
SET NOCOUNT ON;
20+
BEGIN
1421

15-
--------------------------------------------------------------------------------------------------------------------------------
16-
-- SELECT MOST RECENT ATTRIBUTES
17-
--------------------------------------------------------------------------------------------------------------------------------
18-
SELECT AttributeKey,
22+
--------------------------------------------------------------------------------------------------------------------------------
23+
-- SETUP INSERT
24+
--------------------------------------------------------------------------------------------------------------------------------
25+
INSERT
26+
INTO @Attributes
27+
28+
--------------------------------------------------------------------------------------------------------------------------------
29+
-- SELECT MOST RECENT ATTRIBUTES
30+
--------------------------------------------------------------------------------------------------------------------------------
31+
SELECT AttributeKey,
1932
AttributeValue,
2033
0 AS IsExtendedAttribute,
2134
Version
22-
FROM AttributeIndex
23-
WHERE TopicID = @TopicID
35+
FROM AttributeIndex
36+
WHERE TopicID = @TopicID
2437

25-
UNION
38+
UNION
2639

27-
--------------------------------------------------------------------------------------------------------------------------------
28-
-- PARSE MOST RECENT EXTENDED ATTRIBUTES
29-
--------------------------------------------------------------------------------------------------------------------------------
30-
SELECT Attributes.Loc.value(
40+
--------------------------------------------------------------------------------------------------------------------------------
41+
-- PARSE MOST RECENT EXTENDED ATTRIBUTES
42+
--------------------------------------------------------------------------------------------------------------------------------
43+
SELECT Attributes.Loc.value(
3144
'@key',
32-
'VARCHAR(255)'
45+
'VARCHAR(128)'
3346
) AS AttributeKey,
3447
Attributes.Loc.value(
3548
'.[1]',
3649
'VARCHAR(MAX)'
3750
) AS AttributeValue,
3851
1 AS IsExtendedAttribute,
3952
Version
40-
FROM ExtendedAttributeIndex
41-
CROSS APPLY AttributesXml.nodes(
53+
FROM ExtendedAttributeIndex
54+
CROSS APPLY AttributesXml.nodes(
4255
'/attributes/attribute'
4356
) AS Attributes(Loc)
44-
WHERE TopicID = @TopicID
45-
ORDER BY AttributeKey
57+
WHERE TopicID = @TopicID
58+
ORDER BY AttributeKey
59+
60+
------------------------------------------------------------------------------------------------------------------------------
61+
-- RETURN
62+
------------------------------------------------------------------------------------------------------------------------------
63+
RETURN
64+
65+
END

‎OnTopic.Data.Sql.Database/OnTopic.Data.Sql.Database.sqlproj‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,7 @@
8989
<Build Include="Utilities\Views\UniqueKeyIndex.sql" />
9090
<Build Include="Utilities\Views\LeftRightRange.sql" />
9191
<Build Include="Utilities\Tables\AdjacencyList.sql" />
92+
<Build Include="Functions\GetAttributes.sql" />
9293
</ItemGroup>
9394
<ItemGroup>
9495
<Build Include="Tables\ExtendedAttributes.sql" />
@@ -105,7 +106,6 @@
105106
<Build Include="Stored Procedures\UpdateTopic.sql" />
106107
<Build Include="Stored Procedures\UpdateRelationships.sql" />
107108
<Build Include="Stored Procedures\GetTopics.sql" />
108-
<Build Include="Stored Procedures\GetAttributes.sql" />
109109
<Build Include="Stored Procedures\DeleteTopic.sql" />
110110
<Build Include="Stored Procedures\CreateTopic.sql" />
111111
<Build Include="Stored Procedures\GetTopicVersion.sql" />

0 commit comments

Comments
 (0)