SaveToDB Framework Procedures

Procedure	Description
xls.usp_database_permissions	This procedure is an Excel form to manage database-level permissions.
xls.usp_database_permissions_change	This procedure updates a database on cell changes of xls.usp_database_permissions.
xls.usp_object_permissions	This procedure is an Excel form to manage object-level permissions.
xls.usp_object_permissions_change	This procedure updates a database on cell changes of xls.usp_object_permissions.
xls.usp_principal_permissions	This procedure is an Excel form to manage principal permissions.
xls.usp_principal_permissions_change	This procedure updates a database on cell changes of xls.usp_principal_permissions.
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 xls.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_add_framework_developer
xls.xl_actions_add_framework_user
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_remove_framework_developer
xls.xl_actions_remove_framework_user
xls.xl_actions_set_framework_10_mode
xls.xl_actions_set_framework_9_mode
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.xl_parameter_values_principal	This procedure selects database principals for Excel ribbon parameters.
xls.xl_parameter_values_schema	This procedure selects schemas for Excel ribbon parameters.
xls.xl_update_table_format

xls.usp_database_permissions

This procedure is an Excel form to manage database-level permissions.

Use this form to check and manage database-level permissions.

The form uses actual user permissions to show and change permissions.
So, you may grant execute permissions to business users to check permissions themselves.

xls.usp_database_permissions_change

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

Parameter	DataType	Mode	Comment
@column_name	nvarchar(128)	IN	The column name of the edited cell (expected a permission name)
@cell_value	int	IN	Possible values and applied commands: - NULL - REVOKE - G or GRANT - GRANT - G+ or GRANT+ - GRANT WITH GRANT OPTION - D or DENY - DENY - D+ or DENY+ - DENY CASCADE - R or REVOKE - REVOKE - R- or REVOKE- - REVOKE WITH GRANT OPTION FOR - R+ or REVOKE+ - REVOKE CASCADE
@principal	nvarchar(128)	IN	Database principal (user or role)

xls.usp_object_permissions

This procedure is an Excel form to manage object-level permissions.

Parameter	DataType	Mode	Comment
@principal	nvarchar(128)	IN	Database principal (user or role) to filter data
@schema	nvarchar(128)	IN	Database schema or object schema to filter data
@type	nvarchar(128)	IN	Object type to filter data: - schema - table - view - procedure - function
@has_any	bit	IN	Value to filter data: 1 - an object has any permission 0 - an object has no any permision
@has_direct	bit	IN	Value to filter data: 1 - an object has any direct permission 0 - an object has no any direct permision

Use this form to check and manage object-level permissions.

The form uses actual user permissions to show and change permissions.
So, you may grant execute permissions to business users to check permissions themselves.

xls.usp_object_permissions_change

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

Parameter	DataType	Mode	Comment
@column_name	nvarchar(128)	IN	The column name of the edited cell (expected a permission name)
@cell_value	int	IN	Possible values and applied commands: - NULL - REVOKE - G or GRANT - GRANT - G+ or GRANT+ - GRANT WITH GRANT OPTION - D or DENY - DENY - D+ or DENY+ - DENY CASCADE - R or REVOKE - REVOKE - R- or REVOKE- - REVOKE WITH GRANT OPTION FOR - R+ or REVOKE+ - REVOKE CASCADE
@principal	nvarchar(128)	IN	Database principal (user or role)
@schema	nvarchar(128)	IN	Database schema or object schema
@name	nvarchar(128)	IN	Database object name

xls.usp_principal_permissions

This procedure is an Excel form to manage principal permissions.

Parameter	DataType	Mode	Comment
@principal	nvarchar(128)	IN	Database principal (user or role)
@name	nvarchar(128)	IN	Database principal (user or role)
@has_any	bit	IN	Value to filter data: 1 - an object has any permission 0 - an object has no any permision

Use this form to check and manage principal permissions.

The form uses actual user permissions to show and change permissions.
So, you may grant execute permissions to business users to check permissions themselves.

xls.usp_principal_permissions_change

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

Parameter	DataType	Mode	Comment
@column_name	nvarchar(128)	IN	The column name of the edited cell (expected a permission name)
@cell_value	int	IN	Possible values and applied commands: - NULL - REVOKE - G or GRANT - GRANT - G+ or GRANT+ - GRANT WITH GRANT OPTION - D or DENY - DENY - D+ or DENY+ - DENY CASCADE - R or REVOKE - REVOKE - R- or REVOKE- - REVOKE WITH GRANT OPTION FOR - R+ or REVOKE+ - REVOKE CASCADE
@principal	nvarchar(128)	IN	Database principal (user or role)
@name	nvarchar(128)	IN	Database principal (user or role)
@type	nvarchar(128)	IN	Principal type: - sql_user - windows_user - application_role - database_role

xls.usp_role_members

This procedure is an Excel form to manage role members.

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

The form uses actual user permissions to show and change permissions.
So, you may grant execute permissions to business users to check permissions themselves.

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 xls.usp_role_members.

Parameter	DataType	Mode	Comment
@column_name	nvarchar(128)	IN	The column name of the edited cell (expected a 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.
@schema	nvarchar(128)	IN	xls.translations.TABLE_SCHEMA
@is_complete	bit	IN

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_add_framework_developer

Parameter	DataType	Mode	Comment
@name	nvarchar(128)	IN

xls.xl_actions_add_framework_user

Parameter	DataType	Mode	Comment
@name	nvarchar(128)	IN
@allow_updating_formats	bit	IN

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
@Create	bit	IN	1 - Generate CREATE commands 0 - Do not generate CREATE commands
@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)
@MySqlStyle	bit	IN	1 - Generate in MySQL style like @column_name 2 - Generate in SQL Server style like @ColumnName

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)
@MySqlStyle	bit	IN	1 - Generate in MySQL style like @column_name 2 - Generate in SQL Server style like @ColumnName

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 procedures.
Developers may also edit the body to solve specific tasks.

xls.xl_actions_remove_framework_developer

Parameter	DataType	Mode	Comment
@name	nvarchar(128)	IN

xls.xl_actions_remove_framework_user

Parameter	DataType	Mode	Comment
@name	nvarchar(128)	IN

xls.xl_actions_set_framework_10_mode

xls.xl_actions_set_framework_9_mode

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
@use_go	bit	IN	1 - Add the GO command 0 - Do not add the GO command

Exporting data using this procedure is a better choice.

It uses special import procedures to merge settings correctly.