X++:
CREATE PROCEDURE [dbo].[CREATEUSERSESSIONS] @clientType int, @sessionType int, @serverid int, @versionid int, @userid nvarchar(8),
@lanExt nvarchar(10),
@manExt nvarchar(10),
@computerName nvarchar(80),
@sid nvarchar(124),
@recid bigint, @startId int, @maxusers int, @licenseType int, @masterId int, @maxClientId int, @dataPartition nvarchar(8),
@sessionid int OUTPUT,
@loginDateTime datetime OUTPUT AS DECLARE @return_val AS int DECLARE @first AS int DECLARE @max_val AS int DECLARE @counter AS int BEGIN
SELECT @sessionid = -1
SELECT @max_val = -1
SELECT @counter = 0
SELECT @loginDateTime = dateadd(ms, -datepart(ms, getutcdate()), getutcdate()) if(NOT exists
(SELECT *
FROM SYSSERVERSESSIONS WITH (NOLOCK)
WHERE SERVERID = @serverid
AND Status = 1)) BEGIN
SELECT @sessionid = -2 RETURN END
SELECT @first = min(SESSIONID)
FROM SYSCLIENTSESSIONS WITH (UPDLOCK,
READPAST)
WHERE STATUS = 0
AND SESSIONID > @maxClientId
AND SESSIONID <> @masterId IF
(SELECT count(*)
FROM SYSCLIENTSESSIONS
WHERE SESSIONID IN (@first)) > 0 BEGIN IF (@licenseType = 0) BEGIN
UPDATE SYSCLIENTSESSIONS
SET STATUS = 1,
VERSION = @versionid,
SERVERID = @serverid,
USERID = @userid,
LOGINDATETIME = @loginDateTime,
SID = @sid,
USERLANGUAGE = @lanExt,
HELPLANGUAGE = @manExt,
CLIENTTYPE = @clientType,
SESSIONTYPE = @sessionType,
CLIENTCOMPUTER = @computerName,
DATAPARTITION = @dataPartition
WHERE SESSIONID IN (@first) END ELSE IF (@licenseType = 1) BEGIN
UPDATE SYSCLIENTSESSIONS
SET STATUS = 1,
VERSION = @versionid,
SERVERID = @serverid,
USERID = @userid,
LOGINDATETIME = @loginDateTime,
SID = @sid,
USERLANGUAGE = @lanExt,
HELPLANGUAGE = @manExt,
CLIENTTYPE = @clientType,
SESSIONTYPE = @sessionType,
CLIENTCOMPUTER = @computerName,
DATAPARTITION = @dataPartition
WHERE SESSIONID IN (@first)
AND (
(SELECT count(SESSIONID)
FROM SYSCLIENTSESSIONS
WHERE CLIENTTYPE = @clientType
AND ((STATUS = 1)
OR (STATUS = 2))) < @maxusers) END ELSE IF (@licenseType = 2) BEGIN
UPDATE SYSCLIENTSESSIONS
SET STATUS = 1,
VERSION = @versionid,
SERVERID = @serverid,
USERID = @userid,
LOGINDATETIME = @loginDateTime,
SID = @sid,
USERLANGUAGE = @lanExt,
HELPLANGUAGE = @manExt,
CLIENTTYPE = @clientType,
SESSIONTYPE = @sessionType,
CLIENTCOMPUTER = @computerName,
DATAPARTITION = @dataPartition
WHERE SESSIONID IN (@first)
AND (
(SELECT count(SESSIONID)
FROM SYSCLIENTSESSIONS
WHERE CLIENTTYPE = @clientType
AND (USERID = @userid)
AND ((STATUS = 1)
OR (STATUS = 2))) > 0
OR
(SELECT count(DISTINCT USERID)
FROM SYSCLIENTSESSIONS
WHERE CLIENTTYPE = @clientType
AND ((STATUS = 1)
OR (STATUS = 2))) < @maxusers) END IF @@ROWCOUNT = 0
SELECT @sessionid = 0 ELSE
SELECT @sessionid = @first END ELSE BEGIN IF (@licenseType = 1) BEGIN IF
(SELECT count(SESSIONID)
FROM SYSCLIENTSESSIONS
WHERE CLIENTTYPE = @clientType
AND ((STATUS = 1)
OR (STATUS = 2))) >= @maxusers
SELECT @sessionid = 0 END ELSE IF (@licenseType = 2) BEGIN IF ((
(SELECT count(DISTINCT USERID)
FROM SYSCLIENTSESSIONS
WHERE CLIENTTYPE = @clientType
AND ((STATUS = 1)
OR (STATUS = 2))) >= @maxusers)
AND (
(SELECT count(SESSIONID)
FROM SYSCLIENTSESSIONS
WHERE CLIENTTYPE = @clientType
AND (USERID = @userid)
AND ((STATUS = 1)
OR (STATUS = 2))) = 0))
SELECT @sessionid = 0 END IF (@sessionid = -1)
OR (@licenseType = 0) BEGIN WHILE (@sessionid = -1
AND @counter < 5) BEGIN
SET @counter = @counter + 1 IF
(SELECT count(SESSIONID)
FROM SYSCLIENTSESSIONS WITH (UPDLOCK)
WHERE STATUS = 0
OR STATUS = 1
OR STATUS = 2
OR STATUS = 3) = 0
SELECT @max_val = @startId ELSE
SELECT @max_val = max(SESSIONID)+1
FROM SYSCLIENTSESSIONS WITH (UPDLOCK) IF (@max_val > 65535)
SELECT @sessionid = -3 ELSE BEGIN
INSERT INTO SYSCLIENTSESSIONS(SESSIONID, SERVERID, VERSION, LOGINDATETIME, USERID, SID, USERLANGUAGE, HELPLANGUAGE, CLIENTTYPE, SESSIONTYPE, RECID, CLIENTCOMPUTER, STATUS, DATAPARTITION)
VALUES(@max_val,
@serverid,
@versionid,
@loginDateTime,
@userid,
@sid,
@lanExt,
@manExt,
@clientType,
@sessionType,
@recid,
@computerName,
1,
@dataPartition) IF @@ROWCOUNT = 0 BEGIN
SELECT @sessionid = -1 END ELSE
SELECT @sessionid = @max_val END END END END END