SaveToDB Framework
for Microsoft SQL Server

Procedures

Procedure	Description
xls.usp_role_members	This procedure is an Excel form to manage role members.
xls.usp_role_members_change	This procedure updates a database on cell changes of xls25.usp_role_members.
xls.usp_translations	This procedure is an Excel form to select and edit translations in all languages.
xls.usp_translations_change	This procedure updates a database on cell changes of xls.usp_translations.
xls.xl_actions_generate_constraints	This procedure generates primary key, unique, and foreign key constraints.
xls.xl_actions_generate_handlers	This procedure generates SaveToDB event handlers.
xls.xl_actions_generate_procedures	This procedure generates edit procedures and cell change handlers.
xls.xl_actions_set_role_permissions	This procedure sets permissions for the xls_users, xls_developers, and xls_formats roles.
xls.xl_delete_translation	This procedure deletes rows of xls.view_all_translations.
xls.xl_export_settings	This procedure exports SaveToDB Framework settings.
xls.xl_import_formats	This procedure imports SaveToDB Framework settings into xls.formats.
xls.xl_import_handlers	This procedure imports SaveToDB Framework settings into xls.handlers.
xls.xl_import_objects	This procedure imports SaveToDB Framework settings into xls.objects.
xls.xl_import_translations	This procedure imports SaveToDB Framework settings into xls.translations.
xls.xl_import_workbooks	This procedure imports SaveToDB Framework settings into xls.workbooks.

xls.usp_role_members

This procedure is an Excel form to manage role members.

Use this form to easily add business users to the xls_users and xls_formats roles.

Note that you may generate a complete workbook for permission management using the Application Workbooks wizard.

xls.usp_role_members_change

This procedure updates a database on cell changes of xls25.usp_role_members.

Parameter	DataType	Mode	Comment
@column_name	nvarchar(128)	IN	The column name of the edited cell (expected role name).
@cell_number_value	int	IN	1 - Add the user to the role 0 - Remove the user from the role
@name	nvarchar(128)	IN	User or role name (value of the name column)
@data_language	char(2)	IN	The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box. This is a predefined parameter.

xls.usp_translations

This procedure is an Excel form to select and edit translations in all languages.

Parameter	DataType	Mode	Comment
@field	nvarchar(128)	IN	The source field of the xls.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT.

Editing data requires the SaveToDB Enterprise edition.

xls.usp_translations_change

This procedure updates a database on cell changes of xls.usp_translations.

Parameter	DataType	Mode	Comment
@column_name	nvarchar(128)	IN	The column name of the edited cell (expected xls.translations.LANGUAGE_NAME).
@cell_value	nvarchar(max)	IN	A new value of the edited cell used to update for the source field.
@TABLE_SCHEMA	nvarchar(128)	IN	xls.translations.TABLE_SCHEMA
@TABLE_NAME	nvarchar(128)	IN	xls.translations.TABLE_NAME
@COLUMN	nvarchar(128)	IN	xls.translations.COLUMN_NAME
@field	nvarchar(128)	IN	The source field of the xls.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT.

xls.xl_actions_generate_constraints

This procedure generates primary key, unique, and foreign key constraints.

Parameter	DataType	Mode	Comment
@Drop	bit	IN	1 - Generate DROP commands 0 - Do not generate DROP commands
@ALTER	bit	IN
@ConstraintType	tinyint	IN	1 - Primary key constraint 2 - Unique constraint 3 - Index 4 - Foreign key constraint
@SCHEMA	nvarchar(128)	IN	Table schema
@TABLE	nvarchar(128)	IN	Table name
@COLUMN	nvarchar(128)	IN	Column name
@REFERENTIAL_SCHEMA	nvarchar(128)	IN	Referenced table schema
@REFERENTIAL_NAME	nvarchar(128)	IN	Referenced table name
@REFERENTIAL_COLUMN	nvarchar(128)	IN	Referenced column name
@ON_UPDATE	nvarchar(128)	IN	CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION)
@ON_DELETE	nvarchar(128)	IN	CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION)
@CONSTRAINT	nvarchar(128)	IN	Constraint name
@ExecuteScript	bit	IN	1 - Execute the script 0 - Do not execute the script (default)
@DataLanguage	char(2)	IN	The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box. This is a predefined parameter.
@SelectCommands	bit	IN	1 - Select generated commands (default if @PrintCommands IS NULL) 0 - Do not select generated commands (default if @PrintCommands IS NOT NULL)
@PrintCommands	bit	IN	1 - Print generated commands (using the PRINT command) 0 - Do not print generated commands (default)

Use the xls.view_primary_keys, xls.view_unique_keys, and xls.view_foreign_keys views to easily add or drop constraints.

These views allow executing the procedure using the context menu.

xls.xl_actions_generate_handlers

This procedure generates SaveToDB event handlers.

Parameter	DataType	Mode	Comment
@BaseTableSchema	nvarchar(128)	IN	Base object schema
@BaseTableName	nvarchar(128)	IN	Base object name
@TargetObjectSchema	nvarchar(128)	IN	Target object schema
@TargetObjectName	nvarchar(128)	IN	Target object name
@HandlerType	int	IN	1 - ValidationList 2 - ParameterValues 3 - ParameterValues with the NULL value 4 - ContextMenu 5 - DoubleClick 6 - SelectionChange 7 - Actions
@GenerateTargetAsView	bit	IN	1 - Generate the target object as a view 0 - Generate the target object as a procedure
@ExecuteScript	bit	IN	1 - Execute the script 0 - Do not execute the script (default)
@RecreateProceduresIfExist	bit	IN	1 - Execute DROP first if the target object exists 0 - Do not execute DROP commands
@DataLanguage	char(2)	IN	The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box. This is a predefined parameter.
@SelectCommands	bit	IN	1 - Select generated commands (default if @PrintCommands IS NULL) 0 - Do not select generated commands (default if @PrintCommands IS NOT NULL)
@PrintCommands	bit	IN	1 - Print generated commands (using the PRINT command) 0 - Do not print generated commands (default)

Members of the xls_developers role may execute this procedure using the Actions menu.

The xls.view_developer_handlers view configures the Actions menu for every database object.

This feature may save a lot of time as the procedure generates complete procedure declarations. Developers may just edit the body to solve specific tasks.

xls.xl_actions_generate_procedures

This procedure generates edit procedures and cell change handlers.

Parameter	DataType	Mode	Comment
@BaseTableSchema	nvarchar(128)	IN	Base object schema
@BaseTableName	nvarchar(128)	IN	Base object name
@SelectObjectSchema	nvarchar(128)	IN	Target schema of generated objects
@SelectObjectName	nvarchar(128)	IN	Target name of the select object. Edit procedures and change handlers get the same name with the standard suffixes.
@GenerateSelectObject	bit	IN	1 - Generate a view or procedure to select data 0 - Do not generate a select object
@GenerateEditProcedures	bit	IN	1 - Generate procedures for INSERT, UPDATE, and DELETE operations 0 - Do not generate edit procedures
@GenerateChangeHandler	bit	IN	1 - Generate a procedure to process cell change events 0 - Do not generate change handler
@GenerateSelectAsView	bit	IN	1 - Generate the target SELECT object as a view 0 - Generate the target SELECT object as a procedure
@ExecuteScript	bit	IN	1 - Execute the script 0 - Do not execute the script (default)
@RecreateProceduresIfExist	bit	IN	1 - Execute DROP first if the target object exists 0 - Do not execute DROP commands
@DataLanguage	char(2)	IN	The SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box. This is a predefined parameter.
@SelectCommands	bit	IN	1 - Select generated commands (default if @PrintCommands IS NULL) 0 - Do not select generated commands (default if @PrintCommands IS NOT NULL)
@PrintCommands	bit	IN	1 - Print generated commands (using the PRINT command) 0 - Do not print generated commands (default)

Members of the xls_developers role may execute this procedure using the Actions menu.

The xls.view_developer_handlers view configures the Actions menu for every database object.

This feature may save a lot of time as the procedure generates ready-to-use procedure.
Developers may also edit the body to solve specific tasks.

xls.xl_actions_set_role_permissions

This procedure sets permissions for the xls_users, xls_developers, and xls_formats roles.

The procedure grants access to the configuration tables to the xls_users role.
These tables are the required run-time objects shipped with SaveToDB Framework 8.11.

Members of the xls_developers role have full access on the xls schema, including all new objects of the SaveToDB Framework 9.

It also grants access on the xls.formats table to members of the xls_formats role.

xls.xl_delete_translation

This procedure deletes rows of xls.view_all_translations.

Parameter	DataType	Mode	Comment
@TABLE_SCHEMA	nvarchar(128)	IN	xls.translations.TABLE_SCHEMA
@TABLE_NAME	nvarchar(128)	IN	xls.translations.TABLE_NAME
@COLUMN_NAME	nvarchar(255)	IN	xls.translations.COLUMN_NAME
@LANGUAGE_NAME	char(2)	IN	xls.translations.LANGUAGE_NAME

See the configuration in the xls.objects table.

xls.xl_export_settings

This procedure exports SaveToDB Framework settings.

Parameter	DataType	Mode	Comment
@part	tinyint	IN	NULL - all 1 - xls.objects 2 - xls.handlers 3 - xls.translations 4 - xls.formats 5 - xls.workbooks
@as_exec_import	bit	IN	0 - export as INSERT commands 1 - export as EXEC insert procedure commands
@sort_by_names	bit	IN	1 - sort by names 0 - sort by id (default)
@schema	nvarchar(128)	IN	Target schema to export settings or NULL to export all settings
@language	char(2)	IN	xls.translations.LANGUAGE_NAME

Exporting data using this procedure is a better choice.

It uses special import procedures to merge settings correctly.