Stored Procedures

Earthlight supports referencing SQL stored procedures for creating, updating and deleting records in tables/views. They can also be used for updating multiple tables (very useful for database view management). To find out more about Stored Procedures, please visit this site:http://msdn.microsoft.com/en-us/library/ms190782.aspx
If you need more information about how to create such a procedure, visit official MSDN library: http://msdn.microsoft.com/en-us/library/aa214299(v=sql.80).aspx

In order to hook up a layer to stored procedures in Microsoft SQLServer database, you will need to go to Layer Properties > Advanced tab and press the Change modification procedures… button.

Change modification procedures… is only available for users with Corporate administrator privileges.
Please follow this link to read more about configuring user roles and permissions Short guide on how to assign Permissions to Roles and Roles to Users

A dialog that contains 3 sections for each procedure type (Insert⁽¹⁾, Update⁽²⁾ and Delete⁽³⁾) will be shown. To configure them, you have to click Change… button for a specified section.

Each procedure has two sections that must be filled – name⁽¹⁾ and parameters⁽²⁾⁽³⁾.

Name – have to be the same as the procedure’s name in the database. A pattern should look like “SCHEMA_NAME.PROCEDURE_NAME” (w/o quotes).
Parameter’s name – is the same as the parameters declared in the procedure in the database.

1
2
3
4
5
6
7
CREATE PROCEDURE [SCHEMA1].[Insert_Address] (
@AddressID BIGINT,
@Postcode nvarchar(10),
@Geometry varbinary(MAX),
@Detail nvarchar(MAX)
)
AS (...)
Parameter’s value – it is a column name expression in the database that contains the actual data (“[Column_Name]”). Layer columns are listed in the pane on the right.

When each procedure is configured and tied to the database, you can add your table/view to the map and add/edit/delete some records to test the procedures.

After doing all the steps, a new point will be added to the view – SCHEMA1.Insert_Address procedure will be executed. It will add a new record to both tables – SCHEMA1.Address and SCHEMA2.Location_Detail.

After updating and removing a record in a view, it can take some time (about 10 seconds) to refresh the map.

Example

To make the configuration process more clear for users, we have created a simple case that shows steps of configuring Earthlight and creating stored procedures.

First step is creating new tables and a new view that references these tables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- Create new schemas
CREATE SCHEMA [SCHEMA1]
GO
CREATE SCHEMA [SCHEMA2]
GO

-- Create tables
CREATE TABLE [SCHEMA1].[Address] (
  [MI_PRINX] [BIGINT] IDENTITY(1,1) NOT NULL,
  [Address_ID] [BIGINT] NOT NULL,
  [Postcode] [nvarchar](10) NULL,
  [Geometry] [geometry] NULL,
  CONSTRAINT [PK_CONSTRAINT] PRIMARY KEY CLUSTERED (
  [MI_PRINX] ASC,
  [Address_ID] ASC
  )
) ON [PRIMARY]
GO

CREATE TABLE [SCHEMA2].[Location_Detail] (
  [Location_Detail_ID] [BIGINT] IDENTITY(1,1) NOT NULL,
  [Address_ID] [BIGINT] NULL,
  [Detail] [nvarchar](MAX) NULL,
  CONSTRAINT [PK_CONSTRAINT] PRIMARY KEY CLUSTERED (
  [Location_Detail_ID] ASC
  )
) ON [PRIMARY]
GO

ALTER TABLE [SCHEMA2].[Location_Detail]
WITH CHECK ADD CONSTRAINT [FK_Address_ID]
FOREIGN KEY ([Location_Detail_ID], [Address_ID])
REFERENCES [SCHEMA1].[Address]([MI_PRINX], [Address_ID])
GO

-- Create a view
CREATE VIEW [SCHEMA1].[Address_View]
AS
SELECT [A].[Address_ID], [A].[Postcode], [LD].[Detail], [A].[Geometry]
FROM [SCHEMA1].[Address] [A]
LEFT OUTER JOIN [SCHEMA2].[Location_Detail] [LD]
ON [A].[Address_ID] = [LD].[Address_ID] AND [A].[Geometry] IS NOT NULL
GO

After the tables and the view are ready for use, it’s time to create stored procedures.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183

-- Create procedures
CREATE PROCEDURE [SCHEMA1].[Insert_Address] (
@AddressID BIGINT,
@Postcode nvarchar(10),
@Geometry varbinary(MAX),
@Detail nvarchar(MAX)
)
AS
BEGIN
  DECLARE
@ReturnState INT,
@ReturnMessage nvarchar(4000),
@NumRecords INT,
@AddressGeometry geometry

  SET @AddressGeometry = geometry::STGeomFromWKB(@Geometry, 27700)

  SELECT @NumRecords = COUNT(*)
  FROM [SCHEMA1].[Address]
  WHERE [SCHEMA1].[Address].[Address_ID]  = @AddressID

  IF (@NumRecords > 0)
  BEGIN
  SET @ReturnMessage = 'Record with this ID already exists'
  SET @ReturnState = -1
  END
  ELSE
  BEGIN
  BEGIN TRANSACTION
  BEGIN TRY
  INSERT INTO [SCHEMA1].[Address] (
  [Address_ID],
  [Postcode],
  [Geometry]
  )
  VALUES (
@AddressID,
@Postcode,
@AddressGeometry
  )

  INSERT INTO [SCHEMA2].[Location_Detail] (
  [Address_ID],
  [Detail]
  )
  VALUES (
@AddressID,
@Detail
  )

COMMIT
  END TRY
  BEGIN CATCH
  IF(@@TRANCOUNT > 0)
  BEGIN
  ROLLBACK
  END
  SET @ReturnMessage = ERROR_MESSAGE()
  SET @ReturnState = ERROR_NUMBER()
  END CATCH

  SET @ReturnMessage = 'Completed Successfully'
  SET @ReturnState = 0
  END

  SELECT @ReturnState, @ReturnMessage, @AddressID
END
GO

CREATE PROCEDURE [SCHEMA1].[Update_Address] (
@AddressID BIGINT,
@Postcode nvarchar(10),
@Geometry varbinary(MAX),
@Detail nvarchar(MAX)
)
AS
BEGIN
  DECLARE
@ReturnState INT,
@ReturnMessage nvarchar(4000),
@NumRecords INT,
@AddressGeometry geometry

  SELECT @NumRecords = COUNT(*)
  FROM [SCHEMA1].[Address]
  WHERE [SCHEMA1].[Address].[Address_ID]  = @AddressID

  IF (@NumRecords = 0)
  BEGIN
  SET @ReturnMessage = 'Record with this ID does not exist'
  SET @ReturnState = -1
  END
  ELSE
  BEGIN
  BEGIN TRANSACTION
  BEGIN TRY
  IF (@Postcode IS NOT NULL)
  BEGIN
  UPDATE [SCHEMA1].[Address]
  SET [SCHEMA1].[Address].[Postcode] = @Postcode
  WHERE [SCHEMA1].[Address].[Address_ID] = @AddressID
  END

  IF (@Geometry IS NOT NULL)
  BEGIN
  UPDATE [SCHEMA1].[Address]
  SET [SCHEMA1].[Address].[Geometry] = @AddressGeometry
  WHERE [SCHEMA1].[Address].[Address_ID] = @AddressID
  END

  IF (@Detail IS NOT NULL)
  BEGIN
  UPDATE [SCHEMA2].[Location_Detail]
  SET [SCHEMA2].[Location_Detail].[Detail] = @Detail
  WHERE [SCHEMA2].[Location_Detail].[Address_ID] = @AddressID
  END

COMMIT
  END TRY
  BEGIN CATCH
  IF(@@TRANCOUNT > 0)
  BEGIN
  ROLLBACK
  END
  SET @ReturnMessage = ERROR_MESSAGE()
  SET @ReturnState = ERROR_NUMBER()
  END CATCH

  SET @ReturnMessage = 'Completed Successfully'
  SET @ReturnState = 0
  END

  SELECT @ReturnState, @ReturnMessage, @AddressID
END
GO

CREATE PROCEDURE [SCHEMA1].[Delete_Address] (
@AddressID BIGINT
)
AS
BEGIN
  DECLARE
@ReturnState INT,
@ReturnMessage nvarchar(4000),
@NumRecords INT

  SELECT @NumRecords = COUNT(*)
  FROM [SCHEMA1].[Address]
  WHERE [SCHEMA1].[Address].[Address_ID]  = @AddressID

  IF (@NumRecords = 1)
  BEGIN
  BEGIN TRANSACTION
  BEGIN TRY
  DELETE FROM [SCHEMA2].[Location_Detail]
  WHERE [SCHEMA2].[Location_Detail].[Address_ID] = @AddressID

  DELETE FROM [SCHEMA1].[Address]
  WHERE [SCHEMA1].[Address].[Address_ID] = @AddressID

COMMIT
  END TRY
  BEGIN CATCH
  IF(@@TRANCOUNT > 0)
  BEGIN
  ROLLBACK
  END
  SET @ReturnMessage = ERROR_MESSAGE()
  SET @ReturnState = ERROR_NUMBER()
  END CATCH

  SET @ReturnMessage = 'Completed Successfully'
  SET @ReturnState = 0
  END
  ELSE
  BEGIN
  SET @ReturnMessage = 'Address ID not found'
  SET @ReturnState = -1
  END

  SELECT @ReturnState, @ReturnMessage, @AddressID
END
GO

Now, the database stored procedures can be used in Earthlight.

Other usage example

Stored procedures give big possibilities of usage. They can be used in many interesting cases. One of them can be the one described below.

Sometimes some fields from a newly added record need to be filled. Good example is a simple table that contains some map objects. To add a new map object, you have to set its name. Additionally you would like to set the closest postcode for newly added point. It’s a problem, because you would have to find and set it manually. But you can create a stored procedure for the insert operation that finds the closest postcode and sets it automatically while a new record is being added.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

-- Drop procedure if any exists
IF (object_id('TEST_SCHEMA.Insert_Object') IS NOT NULL)
BEGIN
  DROP PROCEDURE [TEST_SCHEMA].[Insert_Object]
END
GO

-- Drop table if any exists
IF (object_id('TEST_SCHEMA.Object') IS NOT NULL)
BEGIN
  DROP TABLE [TEST_SCHEMA].[Object]
END
GO

-- Drop schema if exists
IF (EXISTS (SELECT * FROM sys.schemas WHERE name = 'TEST_SCHEMA'))
BEGIN
  DROP SCHEMA [TEST_SCHEMA]
END
GO

-- Create a new schema
CREATE SCHEMA [TEST_SCHEMA]
GO

-- Create a table
CREATE TABLE [TEST_SCHEMA].[Object] (
  [MI_PRINX] [BIGINT] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](50) NOT NULL UNIQUE,
  [Postcode] [nvarchar](10) NULL,
  [Geometry] [geometry] NULL,
  CONSTRAINT [PK_CONSTRAINT] PRIMARY KEY CLUSTERED (
  [MI_PRINX] ASC
  )
) ON [PRIMARY]
GO

-- Create a procedure
CREATE PROCEDURE [TEST_SCHEMA].[Insert_Object] (
@Name nvarchar(50),
@Geometry varbinary(MAX)
)
AS
BEGIN
  DECLARE
@ReturnState INT,
@ReturnMessage nvarchar(4000),
@NumRecords INT,
@Postcode nvarchar(10),
@AddressGeometry geometry

  SET @AddressGeometry = geometry::STGeomFromWKB(@Geometry, 27700)

  SELECT @NumRecords = COUNT(*)
  FROM [TEST_SCHEMA].[Object]
  WHERE [TEST_SCHEMA].[Object].[Name] = @Name

  IF (@NumRecords > 0)
  BEGIN
  SET @ReturnMessage = 'Record with this name already exists'
  SET @ReturnState = -1
  END
  ELSE
  BEGIN
  BEGIN TRANSACTION
  BEGIN TRY
  SELECT TOP 1 @Postcode = POSTCODE
  FROM dbo.Some_Gazetteer_Layer
  WHERE ADDRESS_LOCATION.STDistance(@AddressGeometry) IS NOT NULL
  ORDER BY ADDRESS_LOCATION.STDistance(@AddressGeometry);

  INSERT INTO [TEST_SCHEMA].[Object] (
  [Name],
  [Postcode],
  [Geometry]
  )
  VALUES (
@Name,
@Postcode,
@AddressGeometry
  )

COMMIT
  END TRY
  BEGIN CATCH
  IF(@@TRANCOUNT > 0)
  BEGIN
  ROLLBACK
  END
  SET @ReturnMessage = ERROR_MESSAGE()
  SET @ReturnState = ERROR_NUMBER()
  END CATCH

  SET @ReturnMessage = 'Completed Successfully'
  SET @ReturnState = 0
  END

  SELECT @ReturnState, @ReturnMessage, @Name
END
GO

First a new point has been added. The nearest gazetteer point is the one visible in the image.

After saving and inspecting the point, you can notice that a postcode is filled and it’s equal to the postcode from the nearest gazetteer record.