# script: scripts/07_module_scope.sql | database: lab-cst | started (UTC): 2026-10-16 01:53:00 (re-run)
/* 07 - Module scope of SET TIME ZONE (CST database)
   Article claim: SET TIME ZONE inside a stored procedure or dynamic SQL reverts when the module exits.
   Also probes: nested procs, a proc that fails after SET, triggers, and scalar functions. */
SET NOCOUNT ON;
PRINT '=== 07 Module scope on ' + DB_NAME();
=== 07 Module scope on lab-cst
CREATE OR ALTER PROCEDURE tzlab.usp_Nested
AS
BEGIN
    SET NOCOUNT ON;
    SET TIME ZONE 'Nepal Standard Time';
    SELECT '   nested proc (set Nepal)' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
END;
CREATE OR ALTER PROCEDURE tzlab.usp_ReportInTokyo
AS
BEGIN
    SET NOCOUNT ON;
    SELECT '  outer proc on entry' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
    SET TIME ZONE 'Tokyo Standard Time';
    SELECT '  outer proc after SET Tokyo' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
    EXEC tzlab.usp_Nested;
    SELECT '  outer proc after nested returns' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
END;
CREATE OR ALTER PROCEDURE tzlab.usp_SetThenFail
AS
BEGIN
    SET NOCOUNT ON;
    SET TIME ZONE 'Tokyo Standard Time';
    SELECT '  failing proc after SET Tokyo' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
    THROW 50001, 'Simulated failure after SET TIME ZONE', 1;
END;
CREATE OR ALTER PROCEDURE tzlab.usp_NoSet
AS
BEGIN
    SET NOCOUNT ON;
    SELECT '  proc without SET inherits caller' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;
END;
PRINT '--- 07.1 Caller on DB setting -> proc sets Tokyo -> nested sets Nepal';
SELECT '07.1 before EXEC' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
EXEC tzlab.usp_ReportInTokyo;
SELECT '07.1 after EXEC (expect DB setting)' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
--- 07.1 Caller on DB setting -> proc sets Tokyo -> nested sets Nepal
step|tz_id
----|-----
07.1 before EXEC|Central Standard Time

step|tz_id|now_offset
----|-----|----------
  outer proc on entry|Central Standard Time|2026-10-15 20:53:01.2878560 -05:00

step|tz_id|now_offset
----|-----|----------
  outer proc after SET Tokyo|Tokyo Standard Time|2026-10-16 10:53:01.2878560 +09:00

step|tz_id|now_offset
----|-----|----------
   nested proc (set Nepal)|Nepal Standard Time|2026-10-16 07:38:01.2878560 +05:45

step|tz_id|now_offset
----|-----|----------
  outer proc after nested returns|Tokyo Standard Time|2026-10-16 10:53:01.2878560 +09:00

step|tz_id
----|-----
07.1 after EXEC (expect DB setting)|Central Standard Time

PRINT '--- 07.2 Caller session overridden to India -> after EXEC should be India again';
SET TIME ZONE 'India Standard Time';
EXEC tzlab.usp_ReportInTokyo;
SELECT '07.2 after EXEC (expect India)' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
--- 07.2 Caller session overridden to India -> after EXEC should be India again
step|tz_id|now_offset
----|-----|----------
  outer proc on entry|India Standard Time|2026-10-16 07:23:01.3304178 +05:30

step|tz_id|now_offset
----|-----|----------
  outer proc after SET Tokyo|Tokyo Standard Time|2026-10-16 10:53:01.3304178 +09:00

step|tz_id|now_offset
----|-----|----------
   nested proc (set Nepal)|Nepal Standard Time|2026-10-16 07:38:01.3304178 +05:45

step|tz_id|now_offset
----|-----|----------
  outer proc after nested returns|Tokyo Standard Time|2026-10-16 10:53:01.3304178 +09:00

step|tz_id
----|-----
07.2 after EXEC (expect India)|India Standard Time

PRINT '--- 07.3 A proc that does not SET inherits the caller (India)';
EXEC tzlab.usp_NoSet;
SET TIME ZONE LOCAL;
--- 07.3 A proc that does not SET inherits the caller (India)
step|tz_id|now_offset
----|-----|----------
  proc without SET inherits caller|India Standard Time|2026-10-16 07:23:01.3734015 +05:30

PRINT '--- 07.4 Proc that THROWs after SET TIME ZONE (caught by caller)';
BEGIN TRY
    EXEC tzlab.usp_SetThenFail;
END TRY
BEGIN CATCH
    SELECT '07.4 in CATCH' AS step, ERROR_NUMBER() AS err_no, ERROR_MESSAGE() AS err_msg, CURRENT_TIMEZONE_ID() AS tz_id;
END CATCH;
SELECT '07.4 after TRY/CATCH (expect DB setting)' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
--- 07.4 Proc that THROWs after SET TIME ZONE (caught by caller)
step|tz_id
----|-----
  failing proc after SET Tokyo|Tokyo Standard Time

step|err_no|err_msg|tz_id
----|------|-------|-----
07.4 in CATCH|50001|Simulated failure after SET TIME ZONE|Central Standard Time

step|tz_id
----|-----
07.4 after TRY/CATCH (expect DB setting)|Central Standard Time

PRINT '--- 07.5 Dynamic SQL via sp_executesql';
EXEC sys.sp_executesql N'SET TIME ZONE ''UTC''; SELECT ''  inside sp_executesql'' AS step, CURRENT_TIMEZONE_ID() AS tz_id, SYSDATETIMEOFFSET() AS now_offset;';
SELECT '07.5 after sp_executesql' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
--- 07.5 Dynamic SQL via sp_executesql
step|tz_id|now_offset
----|-----|----------
  inside sp_executesql|UTC|2026-10-16 01:53:01.4572297 +00:00

step|tz_id
----|-----
07.5 after sp_executesql|Central Standard Time

PRINT '--- 07.6 Dynamic SQL via EXEC(string)';
EXEC (N'SET TIME ZONE ''UTC''; SELECT ''  inside EXEC()'' AS step, CURRENT_TIMEZONE_ID() AS tz_id;');
SELECT '07.6 after EXEC()' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
--- 07.6 Dynamic SQL via EXEC(string)
step|tz_id
----|-----
  inside EXEC()|UTC

step|tz_id
----|-----
07.6 after EXEC()|Central Standard Time

PRINT '--- 07.7 Trigger that SETs TIME ZONE UTC and writes GETDATE() into an audit row';
DROP TABLE IF EXISTS tzlab.TriggerAudit, tzlab.TriggerTarget;
CREATE TABLE tzlab.TriggerTarget (id int IDENTITY PRIMARY KEY, note varchar(50), created_local datetime2(3) DEFAULT (GETDATE()));
CREATE TABLE tzlab.TriggerAudit  (id int IDENTITY PRIMARY KEY, target_id int, audit_time datetime2(3), audit_tz sysname);
--- 07.7 Trigger that SETs TIME ZONE UTC and writes GETDATE() into an audit row
CREATE OR ALTER TRIGGER tzlab.trg_TriggerTarget_Audit ON tzlab.TriggerTarget AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET TIME ZONE 'UTC';
    INSERT tzlab.TriggerAudit (target_id, audit_time, audit_tz) SELECT id, GETDATE(), CURRENT_TIMEZONE_ID() FROM inserted;
END;
INSERT tzlab.TriggerTarget (note) VALUES ('row inserted from CST session');
SELECT '07.7 after INSERT (expect DB setting)' AS step, CURRENT_TIMEZONE_ID() AS tz_id;
SELECT t.id, t.note, t.created_local, a.audit_time, a.audit_tz FROM tzlab.TriggerTarget t JOIN tzlab.TriggerAudit a ON a.target_id = t.id;
step|tz_id
----|-----
07.7 after INSERT (expect DB setting)|Central Standard Time

id|note|created_local|audit_time|audit_tz
--|----|-------------|----------|--------
1|row inserted from CST session|2026-10-15 20:53:01.670|2026-10-16 01:53:01.670|UTC

PRINT '--- 07.8 EXPECT ERROR?: SET TIME ZONE inside a scalar function (side-effecting statement)';
--- 07.8 EXPECT ERROR?: SET TIME ZONE inside a scalar function (side-effecting statement)
CREATE OR ALTER FUNCTION tzlab.fn_NowInTokyo()
RETURNS datetimeoffset
AS
BEGIN
    SET TIME ZONE 'Tokyo Standard Time';
    RETURN SYSDATETIMEOFFSET();
END;
Msg 443, Level 16, State 14, Server your-server, Procedure fn_NowInTokyo, Line 5
Invalid use of a side-effecting operator 'SET TIME ZONE' within a function.
PRINT '--- 07.9 Scalar function WITHOUT SET: does it see the caller session zone?';
--- 07.9 Scalar function WITHOUT SET: does it see the caller session zone?
CREATE OR ALTER FUNCTION tzlab.fn_Now() RETURNS datetimeoffset AS BEGIN RETURN SYSDATETIMEOFFSET(); END;
SET TIME ZONE 'India Standard Time';
SELECT '07.9 fn_Now() under India session' AS step, tzlab.fn_Now() AS fn_value, SYSDATETIMEOFFSET() AS direct_value;
SET TIME ZONE LOCAL;
step|fn_value|direct_value
----|--------|------------
07.9 fn_Now() under India session|2026-10-16 07:23:01.9155847 +05:30|2026-10-16 07:23:01.9155847 +05:30

PRINT '--- 07.10 Native SET option check: does a module get its own SET TIME ZONE "snapshot" at CREATE time (like ANSI_NULLS)?';
SELECT OBJECT_NAME(object_id) AS module, uses_ansi_nulls, uses_quoted_identifier
FROM sys.sql_modules WHERE object_id = OBJECT_ID('tzlab.usp_NoSet');
--- 07.10 Native SET option check: does a module get its own SET TIME ZONE "snapshot" at CREATE time (like ANSI_NULLS)?
module|uses_ansi_nulls|uses_quoted_identifier
------|---------------|----------------------
usp_NoSet|1|1

# sqlcmd exit code: 0