-
Notifications
You must be signed in to change notification settings - Fork 2
/
sp_QueryCallsign.sql
19 lines (16 loc) · 985 Bytes
/
sp_QueryCallsign.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE PROCEDURE [dbo].[sp_QueryCallsign] @callsign varchar(10)
AS
BEGIN
Declare @userid int;
Select @userid=Users.ID from Users Where Users.Callsign = @callsign
Select Users.Callsign 'User.Callsign', Users.Fullname 'User.Name', Users.Email 'User.Email', Users.PhoneHome 'User.Phone.Home', Users.PhoneWork 'User.Phone.Work', Users.PhoneCell 'User.Phone.Cell', Users.LastLogin 'User.LastLogin'
, (
SELECT Repeaters.Callsign 'Repeater.Callsign', Repeaters.OutputFrequency 'Repeater.OutputFrequency', Repeaters.City 'Repeater.City', RepeaterStatuses.Status 'Repeater.Status', Repeaters.DateUpdated 'Repeater.DateUpdated'
FROM Repeaters
JOIN RepeaterStatuses on RepeaterStatuses.ID = Repeaters.status
WHERE Repeaters.ID in (Select Permissions.RepeaterId from Permissions where Permissions.UserId = @userid)
OR Repeaters.TrusteeID = @userId
for JSON path) 'User.Repeaters'
From Users where ID = @userid
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
END;