Table of Contents

About

In SQL Server, an anonymous code block can be created with the BEGIN/END block.

Steps

Start SQL Server

The next steps will be using the howto connection. 

tabul service start sqlserver

1 service was started
Name        Type
---------   ------
sqlserver   docker
  • Ping your connection to check that the database is up.
tabul connection ping sqlserver

The connection (sqlserver) has been pinged successfully

The Anonymous Block

This anonymous block is idempotent, it will create the role webuser if it does not exist

The anonymous code block is located at sqlserver/anonymous_block.sql in the howto connection

With the data print command, we can see the content. 

tabul data print --type text --pipe-mode sqlserver/anonymous_block.sql@howto
# --type text disable sql statement parsing
# --pipe-mode disallow the headers

BEGIN
  -- Check if the role already exists before creating it
  IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'webuser' AND type = 'R')
    BEGIN
      CREATE ROLE webuser;
      PRINT 'Role webuser created successfully.';
    END
  ELSE
    BEGIN
      PRINT 'Role webuser already exists.';
    END
END

Note: The print statements are send to the standard error stream.

Execution

tabul data execute '(sqlserver/anonymous_block.sql@howto)@sqlserver'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie subshell)

List of runtime executed
runtime_data_uri                                  exit_code   count   latency   data_uri                                                                   error_message
-----------------------------------------------   ---------   -----   -------   ------------------------------------------------------------------------   -------------
(sqlserver/anonymous_block.sql@howto)@sqlserver           0       1   0.38s     execute/20251110-204934-688-pipe-tabul-data-exec/anonymous_block.log@tmp

Validation

Anonymous block result returns only an execution status. To verify the execution result, you need: 

SELECT name
FROM sys.database_principals
WHERE name = 'webuser'
  AND type = 'R';



tabul data print '(sqlserver/anonymous_block_actual_roles.sql@howto)@sqlserver'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie they start a subshell)

(sqlserver/anonymous_block_actual_roles.sql@howto)@sqlserver
name
-------
webuser
  • or to perform a diff.
tabul data diff \
  sqlserver/anonymous_block_expected_roles.csv@howto \
  '(sqlserver/anonymous_block_actual_roles.sql@howto)@sqlserver'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie they start a subshell)

Diff Summary Report
The data resources are equals.
from                                                 to                                                             equals   record_count   change_count
--------------------------------------------------   ------------------------------------------------------------   ------   ------------   ------------
sqlserver/anonymous_block_expected_roles.csv@howto   (sqlserver/anonymous_block_actual_roles.sql@howto)@sqlserver   ✓                   1              0