Set User Context in DB
CREATE PROCEDURE [COMMON].[usp_tax_set_context_user](
@p_USERNAME varchar(50)
)
AS
DECLARE
@v_CONTEXT_INFO VARBINARY(128)
BEGIN
SET @v_CONTEXT_INFO = CONVERT(VARBINARY(128), @p_USERNAME)
SET CONTEXT_INFO @v_CONTEXT_INFO
END
Get User context from DB
CREATE PROCEDURE [COMMON].[usp_get_context_user]
AS
BEGIN
DECLARE @p_CONTEXT_INFO INT;
BEGIN TRY
SELECT @p_CONTEXT_INFO = CONVERT(INT, REPLACE(CONVERT(VARCHAR(50), CONTEXT_INFO), CHAR(0), '')) -- get CONTEXT_INFO from SYSPROCESSES table
FROM MASTER.DBO.SYSPROCESSES
WHERE SPID = @@SPID
END TRY
BEGIN CATCH
-- check if CONTEXT_INFO is int
IF @@ERROR = 245
BEGIN
-- Context is not set properly.
SET @p_CONTEXT_INFO = 0
END
END CATCH
RETURN @p_CONTEXT_INFO;
END
