Clone / Copy SSIS Catalog Project Configuration – sp_SSISCloneConfiguration GitHub

When you deploy a SSIS project to SSIS Catalog, you have the possibility to Configure the deployed SSIS project so you do not need to pass the parameter execution values each time you are executing the the project packages. You can configure the project to use explicitly provided value or you can use reference to Environment Variables.

This is very useful feature, however if you are using multiple environments, like DEV, TEST, QA, PROD, then you have to handle that configuration in each of the environments. To simplify the process of setting up the parameter, you can scrip the configuration.

The pitfall of scripting the configuration is, that you can do that automatically only during initial setup of the configuration. Once you confirm the configuration values in SSMS, there is no possibility to script-out any existing configuration.

sp_SSISCloneConfiguration

sp_SSISCloneConfiguration is a custom stored procedure which takes care about cloning/copying the configurations among different projects in the same SSISDB catalog as well as it provides functionality to generate reusable scripts to easily re-apply the configurations even among different SSIS Servers.

The stored procedure also takes care about sensitive values re-encryption when those are being cloned as SSISS is using different encryption keys for each project in the SSIS catalog, so simple copy of the encrypted values does not work among different projects.

The stored procedure executes in the context of the AllSchemaOwner database user in the SSISDB, which ensure access to all areas of SSISDB including the needed certificates and symmetric keys to allow scripting. The actual re-application of the configuration script is running in the context of the caller as the Internal SSIS catalog stored procedures for configuring projects needs to be executed in the context of windows credentials.

The create scripts grants EXECUTE permission to the sssis_admin database role in the SSISDB catalog so the SSSIS admins can utilize that stored procedure.

Be careful, anyone with EXECUTE permission on the stored procedure is capable of seeing decrypted sensitive values when printing the script. The script by default grants that permission to ssis_admin database role. If you want to keep that functionality only to sysadmins, remove the GRANT at the end of the script.

The complete source code for the sp_SSISCloneConfiguration is available in my SQL-Scripts project on GitHub in the SSISDB folder.

The syntax of the stored procedure is as follow:

sp_SSISCloneConfiguration
    @sourceFolder             = 'Source Folder Name'
   ,@sourceProject            = 'Source Project Name'
   ,@sourceObject             = 'Optional Source Object Name'
   ,@destinationFolder        = 'Optional Destination Folder Name'
   ,@destinationProject       = 'Optional Destination Project Name'
   ,@printScript              = 1 --Specifies whether only script should be printed
   ,@decryptSensitiveInScript = 1 --Specifies whether sensitive values should be decrypted in script

When the procedure copies the configuration, it applies the source configurations to target. If on the target are any other configuration, those are not touched by the cloning script. In order to have exact configuration, the configurations on target project should be reset.

The generated script is also easily reusable as there are two variables in the beginning for specification of the destination folder and project. By simple modification of those variables the script will apply the configuration on different projects in different folders.

Parameters

The procedure has several parameters where some are required and some optional

The core parameters are @sourceFolder, @sourceProject . The @sourceObject@destinationFolder, @destinationProject, @printScript and @decryptSensistiveInScript are optional.

@source Folder

Specifies the source folder where the source project is located

@sourceProject

Specifies the name of the source project from within source folder to be scripted

@sourceObject

Optional name of the Source Object. It specifies name of the object withing the project which configuration should be scripted. It can point to project name or to individual package names. When provided, then only configurations related to that object are cloned or scripted.

@destinationFolder

Name of the folder of destination project to which the configuration should be cloned. This parameter is optional and if not provided than the source folder name is being used.

@destinationProject

Name of the destination project to which the configuration should be coned. This parameter is optional and if not provided, then the name of the source project is used. If the destination is not provided or matches the source project (both folders and projects are the same), then the @printScript parameter is enforced to 1 and script is always generated.

@printScript

Specifies whether a script should be printed into the output window. If yes, then you simply copy the script from the output window and you can apply it immediatelly on other SSIS Instance.

Default value = 0, this means that the script executes within the SSISDB catalog and automatically clones the configuration unless it is enforced when source matches destination or destination is not provided.

@decryptSensitiveInScript

Specifeis whether sensitive values should be decrypted in the script.If yes, then the values are part of the script in plain-text form, however they are marked sensitive and will be encrypted after applying the script.

If the parameter is 0 (Default), then the sensitive values are not scripted out and instead NULL value is being scripted out. User must then fill those missing values prior execution of the script.

Sample Output

Below is sample script generated by the stored procedure when @printScript=1 and @decryptSensitiveInScript=1

RAISERROR(N'sp_SSISCloneConfiguration v0.10 (2016-12-18) (C) 2016 Pavel Pawlowski', 0, 0) WITH NOWAIT;
RAISERROR(N'=====================================================================', 0, 0) WITH NOWAIT;

DECLARE @destinationFolder nvarchar(128) = N'Test' --Specify Destination Folder Name
DECLARE @destinationProject nvarchar(128) = N'DataLoading' --Specify Destination Project Name

--Checking for destination folder existence
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE [name] = @destinationFolder)
BEGIN
RAISERROR(N'Destination folder [%s] does not exists.', 15, 0, @destinationFolder) WITH NOWAIT;
RETURN;
END
--Checking for destination project existence
IF NOT EXISTS(SELECT 1
FROM [SSISDB].[catalog].[projects] p
INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = p.folder_id
WHERE f.name = @destinationFolder AND p.name = @destinationProject)
BEGIN
RAISERROR(N'Destination project [%s]\[%s] does not exists.', 15, 1, @destinationFolder, @destinationProject) WITH NOWAIT;
RETURN;
END

--Project parameters configuration
DECLARE @var sql_variant

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[SourceDB_ConnectionString]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'SourceDB_ConnectionString', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'R', @parameter_value = N'SourceDB_ConnectionString'

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[Interval]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(int, N'120');
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'Interval', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[SecurePassword]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'Pa$$w0rd'); --SENSITIVE
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'SecurePassword', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoad.dtsx]\[SourceTableName]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'Person');
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=30, @parameter_name = N'SourceTableName', @object_name = N'DataLoad.dtsx', @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var
RAISERROR(N'-----------------------------------------------------------------------------', 0, 0) WITH NOWAIT;
RAISERROR(N'There are configurations using Environment varaibles references.', 0, 0) WITH NOWAIT;
RAISERROR(N'DON''T FORGET TO SET ENVIRONMENT REFERENCES for project [%s]\[%s].', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT
RAISERROR(N'-----------------------------------------------------------------------------', 0, 0) WITH NOWAIT;
Advertisements

Generating Partition Function – sp_tblCreatePartitionFunction available don GitHub

If you are working with large databases you have come to situation when you need to partition the large tables.

To be able to partition a table, you need to create a Partition Function. Creation of Partition Function is quite easy, however if you need to create a larger number of partitions at initial phase, it can be pretty annoying as you have to define all the ranges for each partition. Also it is easy to make a type in the list of boundary values and as a result create a partition function with wrong partition alignment.

To simplify that process I’m presenting here a stored procedure which takes care about that and generates the partition function automatically based on input parameters

sp_tblCreatePartitionFunction

As mentioned above, the sp_tblCreatePartitionFunction takes care about generation of the partition function based on input parameters.

You can find complete source code in my SQL-Scripts project on GitHub under the TablesManagement\Partitioning folder.

The stored procedure provides support for generation of ranges based on smallint, int, bigint, date, datetime and datetime2 data types.

Stored procedure generates a partition function with boundary values between @rangeStart and @rangeEnd parameters.

The stored procedure is marked as system stored procedure in the script so it means that it can operate in the context of the current user database.

Parameters

The input parameters define the ranges and the way how the partition function is generated

The available input parameters for the function are @pfName, @rangeStart, @rangeEnd, @boundaryType, @incrementValue, @incrementUnit, @useIntegerDates, @integerFormatType and @printScriptOnly

@pfName

Defines the partition function Name. the parameter data type is nvarchar(128) which corresponds to sysname.

@rangeStart

Defines the starting range for generation of the partition function. The parameter is sql_variant. You have to pass parameter of supported data type for the range. As mentioned above the supported data types are smallint, int, bigint, date, datetime and datetime2.

To simplify specification of the range, you can pass the parameter as string. If the value passed as string represents a DateTime it is automatically converted to datetime data type.

You can also pass a value with a data type specifier. The data type specifier must be first character of the string and the value must follow. The supported data type specifiers are 'D' - date, 'T' - datetime, 'B' - bigint, 'I' - int and 'S' - smallint.

Below are some examples of supported strings passed as @rangeStart :

Sample value Result
'2016-01-01' Converted to datetime 2016-01-01
20160101 Converted to datetime 2016-01-01
D2016-01-01 Converted to date 2016-01-01
T20160101 Converted to datetime 2016-01-01
B1010 Converted to bigint 100
I1010 Converted to int 100
S1010 Converted to smallint 100

The @rangeStart representing a date can be altered during the partition function generation based on the @incrementUnit parameter. The @rangeStart is shifted to the beginning of corresponding unit.. See the @incrementUnit parameter for details.

The @rangeStart is included in the generated ranges.

@rangeEnd

Represents then end of range which should be generated for the partition function. The supported data types are the same as for the @rangeStart. See the @rangeStart for details.

The @rangeEnd is inclusive in the range, but may not be included in the final generated partition function in dependency on the @incrementValue and @incrementUnit parameters. See those parameters for details.

@boundaryType

Specifies the boundary type of the boundary values for the partition function. It can be either LEFT or RIGHT. It defines whether the boundary value is included in the left or right partition respective to the boundary value. Default value is RIGHT.

@incrementValue

Defines increment of the boundary values. This means that the @rangeStart value is incremented by the @incrementValue until @rangeEnd is reached.

@incrementUnit

Specifies the unit of the increment. It is being used only for range values representing date. The allowed units are YEAR, MONTH, WEEK, ISO_WEEK, DAY.

In each iteration step the @rangeStart value is incremented by the @incrementValue number of @incrementUnit.

Also the @incrementUnit may have impact on the @rangeStart value. If the @rangeStart value does not point to the first day of respective unit, it is being automatically shifted to the beginning of corresponding unit based on the @rangeStart value.

Samples of @rangeStart shifting:

@rangeStart @incrementUnit @rangeStart shifted to
'2016-03-17' YEAR '2016-01-01'
'2016-03-17' MONTH '2016-03-01'
'2016-03-17' WEEK '2016-03-14'
'2016-01-01' ISO_WEEK '2015-12-28'

@useIntegerDates

Specifies whether boundary values representing dates in the partition function should be represented as int date types or corresponding date, datetime or datetime2 data type. In case of  @useIntegerDates = 0 the boundary value is always first day of corresponding @incrementUnit.

In case of @useIntegerDates = 1 the boundary value is expressed as integer number and the value representing the range depends on the @integerFormatType parameter. See the @integerFormatType for details

It is being used only for date ranges. Default value is 1.

@integerFormatType

Specifies integer format of date range value. When the partition function is being generated and date ranges are being used, the range value is internally always represented by a first day of corresponding rage. if the @useIntegerDate = 1 the final boundary value is generated based on the format type.

Supported format types are:

FormatType Boundary value formatting
1 yyyyMMdd For example 20160101
2 yyyyxx(x) where xxx(x) correspond to appropriate month, week or day within particular year. For example 2016053 represents a day 53 of year 2016. 201643 represents week or iso_week 43 in year 2016

Default value is 2.

@printScriptOnly

Specifies whether only the partition function CREATE script is being printed or the partition function is being automatically crated.

The default value is 1, this means that only script is printed.

Samples

Below are some sample usages of the stored procedure.

Sample 1

sp_tblCreatePartitionFunction
    @pfName = 'myPf'
   ,@rangeStart = 1
   ,@rangeEnd = 1001
   ,@boundaryType = 'RIGHT'
   ,@incrementvalue = 100
CREATE PARTITION FUNCTION [myPf](int) AS RANGE RIGHT FOR VALUES (
    1
   ,101
   ,201
   ,301
   ,401
   ,501
   ,601
   ,701
   ,801
   ,901
   ,1001
)

Sample 2

sp_tblCreatePartitionFunction
    @pfName = 'myPf'
   ,@rangeStart = '2016-01-01'
   ,@rangeEnd = '2020-12-31'
   ,@boundaryType = 'RIGHT'
   ,@incrementvalue = 1
   ,@incrementUnit = 'YEAR'
   ,@useIntegerDates = 1
   ,@integerFormatType = 2

CREATE PARTITION FUNCTION [myPf](int) AS RANGE RIGHT FOR VALUES (
    2016
   ,2017
   ,2018
   ,2019
   ,2020
)

Sample 3

sp_tblCreatePartitionFunction
    @pfName = 'myPf'
   ,@rangeStart = '2016-01-01'
   ,@rangeEnd = '2016-12-31'
   ,@boundaryType = 'LEFT'
   ,@incrementvalue = 1
   ,@incrementUnit = 'MONTH'
   ,@useIntegerDates = 1
   ,@integerFormatType = 2
CREATE PARTITION FUNCTION [myPf](int) AS RANGE LEFT FOR VALUES (
    20160101
   ,20160201
   ,20160301
   ,20160401
   ,20160501
   ,20160601
   ,20160701
   ,20160801
   ,20160901
   ,20161001
   ,20161101
   ,20161201
)

Sample 4

sp_tblCreatePartitionFunction
    @pfName = 'myPf'
   ,@rangeStart = '2016-01-01'
   ,@rangeEnd = '2016-12-31'
   ,@boundaryType = 'LEFT'
   ,@incrementvalue = 1
   ,@incrementUnit = 'MONTH'
   ,@useIntegerDates = 0
CREATE PARTITION FUNCTION [myPf](datetime) AS RANGE LEFT FOR VALUES (
    '20160101'
   ,'20160201'
   ,'20160301'
   ,'20160401'
   ,'20160501'
   ,'20160601'
   ,'20160701'
   ,'20160801'
   ,'20160901'
   ,'20161001'
   ,'20161101'
   ,'20161201'
)

Clone / Copy SSIS Server Variables Among Environments – sp_SSISCloneEnvironment on GitHub

You have SSIS projects deployed in the SSISDB catalog. You are using environments and environment variables for passing parameter values to SSIS Executables. This is a very common scenario.

You come to a situation that you need transfer the SSIS projects, environments and varaibles among different SSIS environments (DEV/TEST/QA/PROD). You already have bunch of environment variables defined in one of your environment and you need to transfer those to other environment.

Unfortunately there is no easy way to clone, copy or script the varaibles to transfer them to different environment. If you do not script them during creation in the SSMS, there is no option in SSMS to script them out. If you do not want to type all the varaibles manually again, you have to script those in some way.

Anyway the proper scripting or clonning is not completely trivial in case sensitive varaible values are being used as SSISDB uses multiple symmetric keys and certificates for the encryption and each Environment in the SSISDB has its own key and corresponding certificate. When cloning such sensitive value, the value needs to be decrypted with the source environment symmetric key and re-encrypted by the destination environment symmetric key.

sp_SSISCloneEnvironment

sp_SSISCloneEnvironment is a custom stored procedure which takes care about cloning the SSSI server variables among different environments in the same SSISDB catalog as well as it allows generate a script, which can be re-applied on even oon other SSIS Instance.

The stored procedure takes care about sensitive values re-encryption by proper certificate when clonning. As mentioned above, SSIS generates a separate symmetric key and certificate for each environment. To ensure proper future functionality, the stored procedure takes care about decryption of the sensitive variables when reading from current environment and re-encryption by proper destination environment symmetric key.

The stored procedure executes in the context of the AllSchemaOwner database user in the SSISDB, which ensure access to all areas of SSISDB including the needed certificates and symmetric keys. Only in areas where it needs to create a folder or environment it switches the context to the caller of the stored procedure as the procedures which are taking care abut the folder and environment creation must be executed under windows credentials.

The create scripts grants EXECUTE permission to the sssis_admin database role in the SSISDB catalog so the SSSIS admins can utilize that stored procedure.

Be carefull, anyone with EXECUTE permission on the stored procedure is capable of seeing decrypted sensitive values when printing the script. The script by default grants that permission to ssis_admin database role. If you want to keep that functionality only to sysadmins, remove the GRANT at the end of the script.

The complete source code for the sp_SSISCloneEnvironment is available in my SQL-Scripts project on GitHub in the SSISDB folder.

The syntax of the stored procedure is as follow:

sp_SSISCloneEnvironment
   @sourceFolder                = 'Source Folder Name'
  ,@sourceEnvironment           = 'Source Environment Name'
  ,@destinationFolder           = 'Destination Folder Name'
  ,@destinationEnvironment      = 'Destination Environment Name'
  ,@autoCreate                  = 1 --Auto Create Destination
  ,@printScript                 = 1 --Specifies that Create Script should be output instead of actual execution
  ,@decryptSensitiveInScript    = 1 --Specifies that the sensitive values should be decrypted in the create script

Currently when cloning the environment variables the destination environment must be empty if script is not being generated and the procedure is running in the non-interactive mode.

The generated script is also easily reusable as there are two variables in the beginning for specification of the destination folder and environment. By simple modification of those variables the script will generate the variables different environments.

Parameters

The procedure has several parameters where some are required and some optional

The core parameters are @sourceFolder, @sourceEnvironment and @destinationFolder. The @destinationEnvornment, @autoCreate, @printScript and @decryptSensistiveInScript are optional.

@source Folder

Specifies the source folder where the source environment is located

@sourceEnvironment

Specifies the name of the source environment from within source folder to be scripted

@destinationFolder

Name of the destination folder to wich the environment varaibles should be clonned.

@destinationEnvironment

Name of the destination environment to which the varaibles should be conned. This parameter is optional and if not provided, then the name of the source environment is used.

@autoCreate

Specifies whether the destination foolde and destination environment should be automatically crated if they do not exist. If the script is being generated then a statement which checks existence of the folder and environment are included in the script and missing folder and/or environment is automatically created if needed by the script.

Default value is 1 = true

@printScript

Specifies whether a script should be printed into the output window. If yes, then you simply copy the script from the output window and you can apply it immediatelly on other SSIS Instance.

Default value = 0, this means that the script executes within the SSISDB catalog and automatically clnes the variables

@decryptSensitiveInScript

Specifeis whether sensitive values should be decrypted in the script.If yes, then the values are part of the script in plain-text form, howver they are marked sensitive and will be encrypted after applying the script.

If the parameter is 0 (Default), then the sensitive values are not scripted out and instead NULL value is being scripted out. User must then fill those missing values priorexecution of the script.

Sample Output

Below is sample script generated by the stored procedure when @printScript=1 and @decryptSensitiveInScript=1

RAISERROR(N'sp_SSISCloneEnvironment v0.20 (2016-10-11) (C) 2016 Pavel Pawlowski', 0, 0) WITH NOWAIT;
RAISERROR(N'===================================================================', 0, 0) WITH NOWAIT;

DECLARE @destinationFolder nvarchar(128) = N'TEST' --Specify Destination Folder Name
DECLARE @destinationEnvironment nvarchar(128) = N'ClonedEnvironment' --Specify Destination Environment Name

--Checking for destination folder existence
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE [name] = @destinationFolder)
BEGIN
    RAISERROR(N'Creating missing Folder [%s]', 0, 0, @destinationFolder) WITH NOWAIT;
    EXEC [SSISDB].[catalog].[create_folder] @folder_name = @destinationFolder
END

--Checking for destination environment existence
IF NOT EXISTS(
    SELECT 1
    FROM [SSISDB].[catalog].[environments] e
    INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
    WHERE f.[name] = @destinationFolder AND e.[name] = @destinationEnvironment
)
BEGIN
    RAISERROR(N'Creating missing Environment [%s]\[%s]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT
    EXEC [SSISDB].[catalog].[create_environment] @folder_name = @destinationFolder, @environment_name = @destinationEnvironment, @environment_description = N''
END

--Checking for variables existence in destination eivnironment
IF EXISTS (
    SELECT 1
    FROM [SSISDB].[catalog].[environment_variables] ev
    INNER JOIN [SSISDB].[catalog].[environments] e ON e.environment_id = ev.environment_id
    INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
    WHERE
        f.name = @destinationFolder AND e.name = @destinationEnvironment
)
BEGIN
    RAISERROR(N'Destination Environment [%s]\[%s] is not empty. Clear all variables prior clonning environment.', 15, 2, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
    RETURN;
END

--Environment variables creation
DECLARE @var sql_variant

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V1]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bit, N'1');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V1', @data_type=N'Boolean', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V10]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is string parameters');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V10', @data_type=N'String', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V11]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is sensitive string'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V11', @data_type=N'String', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V12]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2010-12-31T00:00:00'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V12', @data_type=N'DateTime', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V13]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'99.990000000000000000'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V13', @data_type=N'Decimal', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V14]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'56'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V14', @data_type=N'Int32', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V2]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(tinyint, N'5');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V2', @data_type=N'Byte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V3]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2016-01-01T00:00:00');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V3', @data_type=N'DateTime', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V4]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'12.390000000000000000');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V4', @data_type=N'Decimal', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V5]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'156.987');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V5', @data_type=N'Double', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V6]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'123456');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V6', @data_type=N'Int32', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V7]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bigint, N'987654321');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V7', @data_type=N'Int64', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V8]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(smallint, N'9');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V8', @data_type=N'SByte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V9]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'56');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V9', @data_type=N'Single', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

Cloning user rights in database

update: Check new post SQL Server – Cloning User Rights – updated sp_CloneRights on GitHub

Some times it could happen, that you need to create a new database user, which will have exactly the same right as another existing user.

In ideal scenario, you will have all the necessary rights assigned to a database roles, and then when you create the new user, you simply add the user to appropriate roles to grant all the necessary rights.

This is ideal scenario, which is not always met, especially when you have to manage the server after somebody else, who didn’t used roles for granting rights.

In such scenario a below system stored stored procedure can be very handful.

USE [master]
GO
--============================================
-- Author:      Pavel Pawlowski
-- Created:     2010/04/16
-- Description: Copies rights of old user to new user
--==================================================
CREATE PROCEDURE sp_CloneRights (
    @oldUser sysname, --Old user from which to copy right
    @newUser sysname, --New user to which copy rights
    @printOnly bit = 1, --When 1 then only script is printed on screen, when 0 then also script is executed, when NULL, script is only executed and not printed
    @NewLoginName sysname = NULL --When a NewLogin name is provided also a creation of user is part of the final script
)
AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE #output (
        command nvarchar(4000)
    )

    DECLARE
        @command nvarchar(4000),
        @sql nvarchar(max),
        @dbName nvarchar(128),
        @msg nvarchar(max)

    SELECT
        @sql = N'',
        @dbName = QUOTENAME(DB_NAME())

    IF (NOT EXISTS(SELECT 1 FROM sys.database_principals where name = @oldUser))
    BEGIN
        SET @msg = 'Source user ' + QUOTENAME(@oldUser) + ' doesn''t exists in database ' + @dbName
        RAISERROR(@msg, 11,1)
        RETURN
    END    

    INSERT INTO #output(command)
    SELECT '--Database Context' AS command UNION ALL
    SELECT    'USE' + SPACE(1) + @dbName UNION ALL
    SELECT 'SET XACT_ABORT ON'

    IF (ISNULL(@NewLoginName, '') <> '')
    BEGIN
        SET @sql = N'USE ' + @dbName + N';
        IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @newUser)
        BEGIN
            INSERT INTO #output(command)
            SELECT ''--Create user'' AS command

            INSERT INTO #output(command)
            SELECT
                ''CREATE USER '' + QUOTENAME(@NewUser) + '' FOR LOGIN '' + QUOTENAME(@NewLoginName) +
                    CASE WHEN ISNULL(default_schema_name, '''') <> '''' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(dp.default_schema_name)
                        ELSE ''''
                    END AS Command
            FROM sys.database_principals dp
            INNER JOIN sys.server_principals sp ON dp.sid = sp.sid
            WHERE dp.name = @OldUser
        END'

        EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname, @NewLoginName sysname', @OldUser = @OldUser, @NewUser = @NewUser, @NewLoginName=@NewLoginName
    END

    INSERT INTO #output(command)
    SELECT    '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)

    INSERT INTO #output(command)
    SELECT '--Role Memberships' AS command

    SET @sql = N'USE ' + @dbName + N';
    INSERT INTO #output(command)
    SELECT ''EXEC sp_addrolemember @rolename =''
        + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''''''') + '', @membername ='' + SPACE(1) + QUOTENAME(@NewUser, '''''''') AS command
    FROM    sys.database_role_members AS rm
    WHERE    USER_NAME(rm.member_principal_id) = @OldUser
    ORDER BY rm.role_principal_id ASC'

    EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser

    INSERT INTO #output(command)
    SELECT '--Object Level Permissions'

    SET @sql = N'USE ' + @dbName + N';
    INSERT INTO #output(command)
    SELECT    CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
        + SPACE(1) + perm.permission_name + SPACE(1) + ''ON '' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
        + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name) + '')'' END
        + SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
        + CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END
    FROM    sys.database_permissions AS perm
        INNER JOIN
        sys.objects AS obj
        ON perm.major_id = obj.[object_id]
        INNER JOIN
        sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
        LEFT JOIN
        sys.columns AS cl
        ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
    WHERE    usr.name = @OldUser
    ORDER BY perm.permission_name ASC, perm.state_desc ASC'

    EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser

    INSERT INTO #output(command)
    SELECT N'--Database Level Permissions'

    SET @sql = N'USE ' + @dbName + N';
    INSERT INTO #output(command)
    SELECT    CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
        + SPACE(1) + perm.permission_name + SPACE(1)
        + SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
        + CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END
    FROM    sys.database_permissions AS perm
        INNER JOIN
        sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
    WHERE    usr.name = @OldUser
    AND    perm.major_id = 0
    ORDER BY perm.permission_name ASC, perm.state_desc ASC'

    EXEC sp_executesql @sql, N'@OldUser sysname, @NewUser sysname', @OldUser = @OldUser, @NewUser = @NewUser

    DECLARE cr CURSOR FOR
        SELECT command FROM #output

    OPEN cr

    FETCH NEXT FROM cr INTO @command

    SET @sql = ''

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@printOnly IS NOT NULL)
            PRINT @command

        SET @sql = @sql + @command + CHAR(13) + CHAR(10)
        FETCH NEXT FROM cr INTO @command
    END

    CLOSE cr
    DEALLOCATE cr

    IF (@printOnly IS NULL OR @printOnly = 0)
        EXEC (@sql)

    DROP TABLE #output
END
GO
EXECUTE sp_ms_marksystemobject 'dbo.sp_CloneRights'
GO

The stored procedure allows copying all the objects and database rights from the old user to a new one. It also clones roles membership for the user.

If the @NewLoginName is specified then then it also creates the @newUser in the database for the login specified and then copies the rights.

@printOnly specifies whether the script should be printed, executed automatically or both printed and executed automatically.

As the system is marked as system, it executes in the context of the database in which is executed.

It also allows copying rights among database roles. If you specify as @oldUser a database or application role name, then the rights of that role will be copied to the @newUser. Again the @newUser can be a user name or database/application role name.

For example if you have a Integration services installed and invoke a below script

USE [msdb]
GO
EXEC sp_CloneRights 'db_ssisadmin', 'NewUser'

you will receive a below script for assigning rights.

--Database Context
USE [msdb]
SET XACT_ABORT ON
--Cloning permissions from [db_ssisadmin] to [NewUser]
--Role Memberships
--Object Level Permissions
GRANT DELETE ON [dbo].[sysssislog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_get_dtsversion] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_make_dtspackagename] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_add_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_drop_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_reassign_dtspackageowner] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_get_dtspackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_addlogentry] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_listpackages] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtspackages] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_listfolders] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_deletepackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_deletefolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getpackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getfolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_putpackage] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_checkexists] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_addfolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_renamefolder] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_setpackageroles] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_ssis_getpackageroles] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtspackage_begin] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtspackage_end] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtsstep_begin] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtsstep_end] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_log_dtstask] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtspackagelog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtssteplog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_enum_dtstasklog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtslog_all] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtspackagelog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtssteplog] TO [NewUser]
GRANT EXECUTE ON [dbo].[sp_dump_dtstasklog] TO [NewUser]
GRANT INSERT ON [dbo].[sysssislog] TO [NewUser]
GRANT REFERENCES ON [dbo].[sysssislog] TO [NewUser]
GRANT SELECT ON [dbo].[sysssislog] TO [NewUser]
GRANT UPDATE ON [dbo].[sysssislog] TO [NewUser]
--Database Level Permissions

Hope you will find this script useful and hope it will save you a lot of work when cloning rights.

It saved me several times, when I come to an existing database with several hundreds of tables with rights assigned on the object level and I had to introduce a new user with exactly the same rights as an existing one.

One thing needs to be mentioned for end. The procedure clones only object and database right. It doesn’t clone right for system objects, assemblies etc., but you can easily extend the procedure to cover also this this.

This procedure is inspired by  a script I found in past somewhere on internet.

Automating CRUD procedures generation using T-SQL

CRUD procedures represent a good way how to secure the database. When using CRUD procedures, no user has direct access to the database tables and instead of this is forced to use parameterized procedures for all data manipulations called CRUD procedures. CRUD is acronym for Create Read Update Delete.

CRUD procedures have a lot of advantages from which I can name several:

  • You do not need to grant rights directly to tables and in this way you hide the physical tables structure to users.
  • You can encapsulate some logic into the procedures and then when something needs to be changed, you simply modify a procedure and do not need to rebuild the application.
  • You hide the SQL logic encapsulated in stored procedures from end user.
  • You protect your SQL server against SQL injection as all data using the CRUD procedures has to be passed as parameters and in this way you drastically decrease a chance for the SQL injection.

The main disadvantage of CRUD procedures is the overhead which SQL developer has to do as for each table or group of several tables it is necessary separate stored procedures for the data manipulation.

There are several tools and plugins into visual studio, which simplifies creation of the CRUD procedures, but in this article I would like to show a possible way how to automate creation of the CRUD procedures using pure T-SQL.

The automation will be handled by a custom system stored procedure, which generate all the necessary code or directly create all the CRUD procedures for a database table.

This article describes how to crate a system stored procedure sp_makeCRUD, which will take 4 parameters @objectName, @executionMode, @dropExistingProcedures and @outputIdentityCalcField. The @objectName represents a table for which the CRUD procedures will be generated, @executionMode will represents execution mode of the stored procedure and will control whether the code of CRUD procedures will be only printed on scree or whether the generated code will be automatically executed and the CRUD procedures will created. @dropExistingProcedures parameter will specify whether include DROP statement for all procedures to drop eventual existing procedures and @outputIdentityCalcField will control, whether Identity and Calculated fields will be automatically returned as record set for ISERT and UPDATE operations.

CRUD procedures construction

To create a CRUD procedures we need to know all the fields in the table, whether the fields are Identity or calculated fields and also we need to know what fields the primary key is composed from. Also table name and it’s schema will be needed as user can specify the object name without schema or with schema. The CRUD procedures will be created in the same schema as the original table. To retrieve such information various system tables will be used.

Retrieving Object ID, Table Name, Schema Name and DB Name

To retrieve Object ID (ID of the table) and DB Name we will use the OBJECT_ID() and DB_NAME() functions.

SELECT
    @objID = OBJECT_ID(@objectName),
    @dbName = DB_NAME()

Table name and schema name, could be retrieved using system tables sys.objects and sys.schemas.

SELECT
    @schemaName = s.name,
    @tableName = o.name
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.object_id = @objId AND o.type = 'U'

Retrieving Table Field and Primary Key

After the object ID is retrieved, it is necessary to retrieve all the table fields, their data types and information whether the field is an IDENTITY field or whether it is Calculated Field. This information is necessary for generating INSERT and UPDATE statements as such fields are not updatable. Also it is necessary to identify fields which are part of the primary key. This fields are necessary for the UPDATE, DELETE and also for SELECT One statements. Fields will be temporarily stored in table variables as they will be accessed several times to construct different fields combinations for each statement and also for creation of procedure parameters.

The retrieval of all fields is quite easy as only access to sys.columns table for field names and sys.types for type names is necessary. The field type is written to the table variable as SQL statement which will be used when generating the procedure parameters.

DECLARE @allFields TABLE (        --Table variable for storing all the table fields
    name sysname,                --field name
    isIdentity bit,                --specifies whether field is INDENTITY
    isCalculated bit,            --specifies whether filed is Calculated field
    fieldType sysname            --Specified data type of the field
)

--Get all table fields and store them in the @allFields table variable for construction of CRUD procedures
INSERT INTO @allFields (
    name,
    isIdentity,
    isCalculated,
    fieldType
)
SELECT
    c.name,
    is_identity,
    is_computed,
    CASE
        WHEN t.name IN (N'char', N'nchar', N'varchar', N'nvarchar') THEN QUOTENAME(t.name) + N'(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CAST(c.max_length AS sysname) END + N')'
        WHEN t.name IN (N'decimal', N'numeric') THEN QUOTENAME(t.name) + N'(' + CAST(c.precision AS sysname) + N', ' + CAST(c.scale AS sysname) + N')'
        ELSE QUOTENAME(t.name)
    END
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE object_id = @objId

The query to retrieve the fields form which the Primary Key is composed is more complex and to retrieve all the information we need it is necessary to access several system tables. As the Primary Key is enforced by a unique index, the field names can be found using the ID of the columns in unique index by which the primary key is enforced.

DECLARE @pkFields TABLE (        --Table variable for storing fields which are part of primary key
    name sysname,                --field Name
    fieldType sysname            --Specified data type of the field
)

--Get list of Primary Key Fields and store them in @pkFields table variable for construction of CRUD procedures
INSERT INTO @pkFields(
    name,
    fieldType
)
SELECT
    c.name,
    CASE
        WHEN t.name IN (N'char', N'nchar', N'varchar', N'nvarchar') THEN QUOTENAME(t.name) + N'(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CAST(c.max_length AS sysname) END + N')'
        WHEN t.name IN (N'decimal', N'numeric') THEN QUOTENAME(t.name) + N'(' + CAST(c.precision AS sysname) + N', ' + CAST(c.scale AS sysname) + N')'
        ELSE QUOTENAME(t.name)
    END
FROM sys.key_constraints kc
INNER JOIN sys.indexes i ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE kc.parent_object_id = @objId and kc.type = 'PK'

Generating Columns and Variables Lists

Once the list of all columns is retrieved, the lists of different columns combinations could be generated. For this purpose a SELECT statement with FOR XML PATH clause will be used as this clause greatly simplifies a CSV list generation on SQL 2005 and above. Below is a code for the All Columns

SELECT @allColumns =
    STUFF(REPLACE((SELECT
                        N'            ' + @crlf + N'            ,' + QUOTENAME(c.name)
                    FROM @allFields c
                    FOR XML PATH(N'')
                    ),
            @crlfXML, @crlf),
        1, 27, N'             ')

The spaces are included in the generated string for later final query formatting as it allows the query to be printed in readable form and also when a stored procedure will be generated, it can be later easily modified as the query will contain correct line breaks and indentation. The REPLACE function is used to replace the XML representation of CRLF back to real CRLF characters as the FOR XML PATH in reality produces XML output and translates all the CRLF characters to their XML compliant equivalents.

Generating code of CRUD Procedures

Once all the the combination of columns are generated, the variables with such fields and parameters list can be used to generate the final CRUD procedure. Below is a snippet representing a SELECT procedure.

SET @sql = N'-- =======================================================
-- Author:        ' + QUOTENAME(SUSER_SNAME()) + N'
-- Create date: ' + CONVERT(nvarchar(10), GETDATE(), 111) + N'
-- Description:    Selects records from table ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + N'
-- =======================================================
CREATE PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixSelect + N']
' + @selectParams + N'
AS
BEGIN
    SET NOCOUNT ON;

    IF (' + @selectAllIfNullCondition + N') THEN
    BEGIN
        SELECT
' + @allColumns + N'
        FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N'
    END
    ELSE
    BEGIN
        SELECT
' + @allColumns + N'
        FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N'
        WHERE
' + @selectCondition + N'
    END
END
GO

';

sp_makeCRUD Final Form

Once all the parts of the procedure are prepared it’s possible to construct a final form of the sp_makeCRUD procedure. Below is a complete code for the sp_makeCRUD procedure.

USE [master]
GO
--==========================================================
-- Author: (c) 2011 Pavel Pawlowski
-- Description: Generates CRUD procedures for a table
--
--@objectName = table name with or without schema for which the CRUD procedures should be generated
--
--@executionMode
--  1 = Print the script Only
--  2 = Output the script as recordset using SELECT for longer procedures which is not possible output using PRINT
--  4 = Execute and Commit
--  8 = Execute and Rollback - testing mode
--
--@dropExistingProcedures = 1 | 0
-- specifies whether generate DROP commands for existing objects
--
--@outputIndentityCalcFields = 1 | 0
-- specifies whether Identity and Calculated fields should be OUTPUTed in INSERT and UPDATE
--==========================================================
CREATE PROCEDURE [dbo].[sp_makeCRUD]
    @objectName sysname,
    @executionMode tinyint = 1,
    @dropExistingProcedures bit = 1,
    @outputIndentityCalcFields bit = 1
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    --variables declaration
    DECLARE
        @objId int,                                 --ID of the Table
        @schemaName sysname,                        --schema Name of the Table
        @tableName sysname,                         --TableName
        @dbName sysname,                            --Database name in which we are creating the procedures
        @crlfXML nchar(7),                          --XML representation of the CLRF
        @crlf nchar(2),                             --CLRF characters
        @procPrefix sysname,                        --CRUD procedures Prefix
        @sql nvarchar(max),                         --SQL code for particular steps
        @msg nvarchar(max),                         --A message
        @suffixSelect sysname,                      --Suffix for SELECT procedure
        @suffixUpdate sysname,                      --Suffix for UPDATE procedure
        @suffixDelete sysname,                      --Suffix for Delete procedure
        @suffixInsert sysname,                      --Suffix for INSERT procedure
        @selectParams nvarchar(max),                --Parameters for SELECT procedure
        @allColumns nvarchar(max),                  --List of All columns in a table for SELECT statement
        @selectAllIfNullCondition nvarchar(max),    --Condition for checking if all parameters in SELECT procedure are NULL
        @selectCondition nvarchar(max),             --SELECT statement condition
        @updateParams nvarchar(max),                --Parameters for UPDATE procedure
        @updateColumns nvarchar(max),               --List of columns for UPDATE statement
        @updateDeleteCondition nvarchar(max),       --Condition for UPDATE and DELETE statement
        @updateOutputCols nvarchar(max),            --List of UPDATE statement output columns to output calculated columns
        @deleteParams nvarchar(max),                --Parameters for DELETE procedure
        @insertParams nvarchar(max),                --Parameters for INSERT procedure
        @insertColumns nvarchar(max),               --List of COLUMNS for INSERT statement
        @insertOutputCols nvarchar(max),            --List of INSERT statement ouptup columns to output IDENTITY and calculated fields
        @insertParamNames nvarchar(max),            --List of parameter names in Insert procedure
        @isTooLongForPrint bit                      --Sores info whether some of the procs is too long for PRINT

    --Declaration of fields Table Variables
    DECLARE @pkFields TABLE (        --Table variable for storing fields which are part of primary key
        name sysname,                --field Name
        fieldType sysname            --Specified data type of the field
    )
    DECLARE @allFields TABLE (
        name sysname,                --field name
        isIdentity bit,              --specifies whether field is INDENTITY
        isCalculated bit,            --specifies whether filed is Calculated field
        fieldType sysname            --Specified data type of the field
    )

    --Table variable for storing scripts for execution
    DECLARE @scripts TABLE (
        id int NOT NULL IDENTITY,
        script nvarchar(max)
    )

    --Check if an execution mode is selected
    IF ((@executionMode & 7) = 0)
    BEGIN
        SET @msg = N'You have to select at at leas one possible execution Mode (@executionMode)
    1 = Print the script Only
    2 = Output the script as SELECT resordset for longer procedures which is not possible output using PRINT
    4 = Execute and Commit
    8 = Execute and Rollback - testing mode

You can also combine the Print and Execute Modes, but you cannot combine both execution modes'
        RAISERROR (@msg, 11, 1)
        RETURN
    END

    IF ((@executionMode & 6) = 6)
    BEGIN
        SET @msg = N'You cannot specify Execute and Commit with Execute and Rollback Together'
        RAISERROR (@msg, 11, 1)
        RETURN
    END

    --populate parameters and constants
    SELECT
        @objID = OBJECT_ID(@objectName),
        @dbName = DB_NAME(),
        @crlfXML = N'
' + NCHAR(10),    --XML Representation of the CR+LF delimiter as we use FOR XML PATH ant this translates the original CR+LF to XML Equivalent. We need it to change it back
        @crlf = NCHAR(13) + NCHAR(10),        --CR+LF delimiter used in script
        @procPrefix = 'usp_CRUD_',            --Specifies prefix to be added to all CRUD procedures
        @suffixSelect = '_S',                --Specifies suffix to be added to the Select Procedure
        @suffixUpdate = '_U',                --Specifies suffix to be added to the Update Procedure
        @suffixDelete = '_D',                --Specifies suffix to be added to the Delete Procedure
        @suffixInsert = '_I'                --Specifies suffix to be added to the Inser Procedure

    --Check whether object exists
    IF @objId IS NULL
    BEGIN
        SET @msg = N'Object "' + @objectName + '" doesnt'' exist in database ' + QUOTENAME(@dbName)
        RAISERROR (@msg, 11, 1)
        RETURN
    END

    --Populate table name and schema name
    SELECT
        @schemaName = s.name,
        @tableName = o.name
    FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.object_id = @objId AND o.type = 'U'

    --check whether object is table
    IF (@tableName IS NULL)
    BEGIN
        SET @msg = N'Object "' + @objectName + '" is not User Table. Creating CRUD procedures is possible only on User Tables.'
        RAISERROR (@msg, 11, 1)
        RETURN
    END

    --Get all table fields and store them in the @allFields table variable for construction of CRUD procedures
    INSERT INTO @allFields (
        name,
        isIdentity,
        isCalculated,
        fieldType
    )
    SELECT
        c.name,
        is_identity,
        is_computed,
        CASE
            WHEN t.name IN (N'char', N'nchar', N'varchar', N'nvarchar') THEN QUOTENAME(t.name) + N'(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CAST(c.max_length AS sysname) END + N')'
            WHEN t.name IN (N'decimal', N'numeric') THEN QUOTENAME(t.name) + N'(' + CAST(c.precision AS sysname) + N', ' + CAST(c.scale AS sysname) + N')'
            ELSE QUOTENAME(t.name)
        END
    FROM sys.columns c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE object_id = @objId

    --Get list of Primary Key Fields and store them in @pkFields table variable for construction of CRUD procedures
    INSERT INTO @pkFields(
        name,
        fieldType
    )
    SELECT
        c.name,
        CASE
            WHEN t.name IN (N'char', N'nchar', N'varchar', N'nvarchar') THEN QUOTENAME(t.name) + N'(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CAST(c.max_length AS sysname) END + N')'
            WHEN t.name IN (N'decimal', N'numeric') THEN QUOTENAME(t.name) + N'(' + CAST(c.precision AS sysname) + N', ' + CAST(c.scale AS sysname) + N')'
            ELSE QUOTENAME(t.name)
        END
    FROM sys.key_constraints kc
    INNER JOIN sys.indexes i ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id
    INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
    WHERE kc.parent_object_id = @objId and kc.type = 'PK'

    --Check Whether there is primary Key the CRUD works only if there is primary key in the table
    IF (NOT EXISTS(SELECT 1 FROM @pkFields))
    BEGIN
        SET @msg = N'Table "' + @objectName + '" does not have a Primary Key. There must exists a primary key prior generating CRUD procedures.'
        RAISERROR (@msg, 11, 1)
    END

    --list of output columns for INSERT statement (ouptup of Identity and calculated fields)
    SELECT @insertOutputCols =
        STUFF(REPLACE((SELECT
                            N'        ' + @crlf + N'        ,inserted.' + QUOTENAME(c.name)
                        FROM @allFields c
                        WHERE isIdentity = 1 OR isCalculated = 1
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 19, N'         ')

    --list of output columns for UPDATE statement (Calculated fields only)
    SELECT @updateOutputCols =
        STUFF(REPLACE((SELECT
                            N'        ' + @crlf + N'        ,inserted.' + QUOTENAME(c.name)
                        FROM @allFields c
                        WHERE isCalculated = 1
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 19, N'         ')        

    --ist of all columns used in the SELECT Statement
    SELECT @allColumns =
        STUFF(REPLACE((SELECT
                            N'            ' + @crlf + N'            ,' + QUOTENAME(c.name)
                        FROM @allFields c
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 27, N'             ')

    --list of columns for UPDATE statement including the equal sign and variable (all columns except indentity and calculated ones)
    SELECT @updateColumns =
        STUFF(REPLACE((SELECT
                            N'        ' + @crlf + N'        ,' + QUOTENAME(c.name) + N' = @' + c.name
                        FROM @allFields c
                        WHERE isIdentity = 0 AND isCalculated = 0
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 19, N'         ')

    --list of columns for INSERT statement (all columns except identity and calculated ones)
    SELECT @insertColumns =
        STUFF(REPLACE((SELECT
                            N'        ' + @crlf + N'        ,' + QUOTENAME(c.name)
                        FROM @allFields c
                        WHERE isIdentity = 0 AND isCalculated = 0
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 19, N'         ')

    --condition for UPDATE and DELETE statement
    SET @updateDeleteCondition =
            STUFF(REPLACE((SELECT
                            N'        ' + @crlf + N'        AND' + @crlf + '        ' + QUOTENAME(c.name) + N' = @' + c.name
                        FROM @pkFields c
                        FOR XML PATH(N'')), @crlfXML, @crlf), 1, 23, N'')

    --IF condition for SELECT statement if all params will be NULL to do not use condition to receive better plans
    SET @selectAllIfNullCondition =
                STUFF((SELECT
                            N' AND @' + c.name + N' IS NULL'
                        FROM @pkFields c
                        FOR XML PATH(N'')), 1, 5, N'')

    --Select condition (for SELECT ONE)
    SET @selectCondition =
        STUFF(REPLACE((SELECT
                            N'            ' + @crlf + N'            AND' + @crlf + N'            (@' + c.name + N' IS NULL OR ' + QUOTENAME(c.name) + N' = @' + c.name + N')'
                        FROM @pkFields c
                        FOR XML PATH(N'')), @crlfXML, @crlf), 1, 31, N'')

    --parameters list for SELECT CRUD procedure
    SELECT @selectParams =
        STUFF(REPLACE((SELECT
                            N',    ' + @crlf + N'    @' + c.name + N' ' + c.fieldType + N' = NULL'
                        FROM @pkFields c
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 11, N'    ')

    --parameters list for DELETE CRUD procedure
    SELECT @deleteParams =
        STUFF(REPLACE((SELECT
                            N',    ' + @crlf + N'    @' + c.name + N' ' + c.fieldType
                        FROM @pkFields c
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 11, N'    ')

    --parameters list for UPDATE CRUD procedure
    SELECT @updateParams =
        STUFF(REPLACE((SELECT
                            N',    ' + @crlf + N'    @' + c.name + N' ' + c.fieldType
                        FROM @allFields c
                        LEFT JOIN @pkFields pk ON c.name = pk.name
                        WHERE (c.isIdentity = 0 AND c.isCalculated = 0) OR pk.name IS NOT NULL
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 11, N'    ')

    --parameters list for INSERT CRUD procedure
    SELECT @insertParams =
        STUFF(REPLACE((SELECT
                            N',    ' + @crlf + N'    @' + c.name + N' ' + c.fieldType
                        FROM @allFields c
                        LEFT JOIN @pkFields pk ON c.name = pk.name
                        WHERE c.isIdentity = 0 AND c.isCalculated = 0
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 11, N'    ')

    --parameter names list for INSERT command in the INSERT CRUD procedure
    SELECT @insertParamNames =
        STUFF(REPLACE((SELECT
                            N'        ' + @crlf + N'        ,@' + c.name
                        FROM @allFields c
                        LEFT JOIN @pkFields pk ON c.name = pk.name
                        WHERE c.isIdentity = 0 AND c.isCalculated = 0
                        FOR XML PATH(N'')
                        ),
                @crlfXML, @crlf),
            1, 19, N'         ')

--USE DB
SET @sql = N'USE ' + QUOTENAME(@dbName) + N'
'
INSERT INTO @scripts(script) VALUES(@sql)

--SELECT PROCEDURE
IF (@dropExistingProcedures = 1)
BEGIN
SET @sql = N'--Drop existing SELECT CRUD Procedure
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixSelect + N']'') AND type = ''P''))
    DROP PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixSelect +N']
'
INSERT INTO @scripts(script) VALUES(@sql)
END

SET @sql = N'-- =======================================================
-- Author:      ' + QUOTENAME(SUSER_SNAME()) + N'
-- Create date: ' + CONVERT(nvarchar(10), GETDATE(), 111) + N'
-- Description: Selects records from table ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + N'
-- =======================================================
CREATE PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixSelect + N']
' + @selectParams + N'
AS
BEGIN
    SET NOCOUNT ON;

    IF (' + @selectAllIfNullCondition + N')
    BEGIN
        SELECT
' + @allColumns + N'
        FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N'
    END
    ELSE
    BEGIN
        SELECT
' + @allColumns + N'
        FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N'
        WHERE
' + @selectCondition + N'
    END
END
';
INSERT INTO @scripts(script) VALUES(@sql)

--UPDATE PROCEDURE
IF (@dropExistingProcedures = 1)
BEGIN
SET @sql = N'--Drop existing UPDATE CRUD Procedure
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixUpdate + N']'') AND type = ''P''))
    DROP PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixUpdate + N']
'
INSERT INTO @scripts(script) VALUES(@sql)
END

SET @sql = N'-- =======================================================
-- Author:      ' + QUOTENAME(SUSER_SNAME()) + N'
-- Create date: ' + CONVERT(nvarchar(10), GETDATE(), 111) + N'
-- Description: Updates record in table ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + N'
-- =======================================================
CREATE PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixUpdate + N']
' + @updateParams + N'
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' SET
' + @updateColumns + CASE WHEN @outputIndentityCalcFields = 1 AND @updateOutputCols <> N'' AND @updateOutputCols IS NOT NULL THEN N'
    OUTPUT
' + @updateOutputCols
    ELSE N'' END + N'
    WHERE
' + @updateDeleteCondition + N'
END
';
INSERT INTO @scripts(script) VALUES(@sql)

--DELETE PROCEDURE
IF (@dropExistingProcedures = 1)
BEGIN
SET @sql = N'--Drop existing DELETE CRUD Procedure
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixDelete + N']'') AND type = ''P''))
    DROP PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName +  @suffixDelete + N']
'
INSERT INTO @scripts(script) VALUES(@sql)
END

SET @sql = N'-- =======================================================
-- Author:      ' + QUOTENAME(SUSER_SNAME()) + N'
-- Create date: ' + CONVERT(nvarchar(10), GETDATE(), 111) + N'
-- Description: Deletes recors from table ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + N'
-- =======================================================
CREATE PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixDelete + N']
' + @deleteParams + N'
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N'
    WHERE
' + @updateDeleteCondition + N'
END
';
INSERT INTO @scripts(script) VALUES(@sql)

--INSERT PROCEDURE
IF (@dropExistingProcedures = 1)
BEGIN
SET @sql = N'--Drop existing INSERT CRUD Procedure
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixInsert + N']'') AND type = ''P''))
    DROP PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixInsert + N']
'
INSERT INTO @scripts(script) VALUES(@sql)
END

SET @sql = N'-- =======================================================
-- Author:      ' + QUOTENAME(SUSER_SNAME()) + N'
-- Create date: ' + CONVERT(nvarchar(10), GETDATE(), 111) + N'
-- Description: Deletes recors from table ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + N'
-- =======================================================
CREATE PROCEDURE ' + QUOTENAME(@schemaName) + N'.[' + @procPrefix + @tableName + @suffixInsert + N']
' + @insertParams + N'
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' (
' + @insertColumns + N'
    )' + CASE WHEN @outputIndentityCalcFields = 1 AND @insertOutputCols <> N'' AND @insertOutputCols IS NOT NULL THEN N'
    OUTPUT
' + @insertOutputCols
    ELSE N'' END + N'
    SELECT
' + @insertParamNames + N'
END
';
INSERT INTO @scripts(script) VALUES(@sql)

DECLARE cr CURSOR FAST_FORWARD FOR
SELECT script FROM @scripts ORDER BY id

--if EXECUTION mode contains 2 we should output the code using SELECT
--Script generate using the SELECT can be saved by right lick on the result and
--select Save Result AS and storing it as CSV
IF ((@executionMode & 2) = 2 OR (@isTooLongForPrint = 1 AND (@executionMode & 1) = 1))
BEGIN
    SELECT script + N'GO' FROM @scripts
END

SET @isTooLongForPrint = ISNULL((SELECT 1 FROM @scripts WHERE LEN(script) > 4000), 0)

--if Execution mode contains 1 we should PRINT the statements
IF ((@executionMode & 1) = 1 AND @isTooLongForPrint = 0)
BEGIN
    OPEN cr
    FETCH NEXT FROM cr INTO @sql
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT @sql
        PRINT 'GO'
        FETCH NEXT FROM cr INTO @sql
    END
    CLOSE cr
END

--Execute the statement if it should be executed
IF (( @executionMode & 4 ) = 4 OR ( @executionMode & 8 ) = 8 )
BEGIN

    OPEN cr

    BEGIN TRY
        BEGIN TRANSACTION

        FETCH NEXT FROM cr INTO @sql
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXEC (@sql)
            FETCH NEXT FROM cr INTO @sql
        END

        IF ((@executionMode & 4) = 4)
        BEGIN
            IF (@@TRANCOUNT > 0)
                COMMIT TRANSACTION
        END
        ELSE
        BEGIN
            IF (@@TRANCOUNT > 0)
                ROLLBACK TRANSACTION
        END
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION
    END CATCH
END

--if cursor is open, close it
IF (cursor_status('global', 'cr') = 1)
    CLOSE cr

DEALLOCATE cr
END
GO

EXECUTE sp_ms_marksystemobject 'dbo.sp_makeCRUD'
Go

The each of the SQL statement in the procedure is first stored in a table variable called @scripts for later printing and/or execution. At the end of the procedure the code is printed using a PRINT statement or using a SELECT statement which is automatically used in case the code for some CRUD procedure is longer than 4000 characters and therefore not possible to PRINT.

If Execute and Commit or Execute And Rollback was selected then the code for the CRUD procedures is also executed. The Execute and Rollback is useful for testing, that the code for the procedures is correct and working.

Procedure is created in the [master] database so it is accessible from all databases and we do not need to create it in every database.

To be fully functional, it is necessary to mark the procedure as system procedure using below command.

EXECUTE sp_ms_marksystemobject 'dbo.sp_makeCRUD'

Marking the procedure as system object is necessary as if we not mark the object, then the procedure will run in context of the database in which it was created – in this case [system] and will fail as there are not the tables for which we want to generate the CRUD procedures.

Once the procedure is marked as system object, then it executes in the context of the current database, even it is located in the [master] database.

After we create  the proc, we can test it on a small example:

USE [tempdb]
GO
CREATE TABLE dbo.TestTable (
    ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name varchar(10),
    Price money,
    Quantity decimal(5,2),
    TotalPrice AS (Price * Quantity)
)
GO
sp_makeCRUD 'dbo.TestTable'

This will produce below code for the CRUD procedures

USE [tempdb]
GO
--Drop existing SELECT CRUD Procedure
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[usp_CRUD_TestTable_S]') AND type = 'P'))
    DROP PROCEDURE [dbo].[usp_CRUD_TestTable_S]
GO
-- =======================================================
-- Author:      [PAVEL-PCPavel]
-- Create date: 2011/02/10
-- Description: Selects records from table [dbo].[TestTable]
-- =======================================================
CREATE PROCEDURE [dbo].[usp_CRUD_TestTable_S]
    @ID [int] = NULL
AS
BEGIN
    SET NOCOUNT ON;

    IF (@ID IS NULL)
    BEGIN
        SELECT
             [ID]
            ,[Name]
            ,[Price]
            ,[Quantity]
            ,[TotalPrice]
        FROM [dbo].[TestTable]
    END
    ELSE
    BEGIN
        SELECT
             [ID]
            ,[Name]
            ,[Price]
            ,[Quantity]
            ,[TotalPrice]
        FROM [dbo].[TestTable]
        WHERE
            (@ID IS NULL OR [ID] = @ID)
    END
END
GO
--Drop existing UPDATE CRUD Procedure
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[usp_CRUD_TestTable_U]') AND type = 'P'))
    DROP PROCEDURE [dbo].[usp_CRUD_TestTable_U]
GO
-- =======================================================
-- Author:      [PAVEL-PCPavel]
-- Create date: 2011/02/10
-- Description: Updates record in table [dbo].[TestTable]
-- =======================================================
CREATE PROCEDURE [dbo].[usp_CRUD_TestTable_U]
    @ID [int],
    @Name [varchar](10),
    @Price [money],
    @Quantity [decimal](5, 2)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [dbo].[TestTable] SET
         [Name] = @Name
        ,[Price] = @Price
        ,[Quantity] = @Quantity
    OUTPUT
         inserted.[TotalPrice]
    WHERE
        [ID] = @ID
END
GO
--Drop existing DELETE CRUD Procedure
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[usp_CRUD_TestTable_D]') AND type = 'P'))
    DROP PROCEDURE [dbo].[usp_CRUD_TestTable_D]
GO
-- =======================================================
-- Author:      [PAVEL-PCPavel]
-- Create date: 2011/02/10
-- Description: Deletes recors from table [dbo].[TestTable]
-- =======================================================
CREATE PROCEDURE [dbo].[usp_CRUD_TestTable_D]
    @ID [int]
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM [dbo].[TestTable]
    WHERE
        [ID] = @ID
END
GO
--Drop existing INSERT CRUD Procedure
IF (EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[usp_CRUD_TestTable_I]') AND type = 'P'))
    DROP PROCEDURE [dbo].[usp_CRUD_TestTable_I]
GO
-- =======================================================
-- Author:      [PAVEL-PCPavel]
-- Create date: 2011/02/10
-- Description: Deletes recors from table [dbo].[TestTable]
-- =======================================================
CREATE PROCEDURE [dbo].[usp_CRUD_TestTable_I]
    @Name [varchar](10),
    @Price [money],
    @Quantity [decimal](5, 2)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[TestTable] (
         [Name]
        ,[Price]
        ,[Quantity]
    )
    OUTPUT
         inserted.[ID]
        ,inserted.[TotalPrice]
    SELECT
         @Name
        ,@Price
        ,@Quantity
END
GO

Using Stored Procedure as Data Source in Excel

When you want to use a SQL Server as external data source in excel, you can find, that it is not possible to select a stored procedure as a source of data in the Data Connection Wizard. This can lead to a conclusion, that it’s not possible to use a Stored Procedure as a source of data in Excel.

Excel Data Connection Wizard

Although, you cannot select a stored procedure, in the Data Connection Wizard, there is a simple workaround who to select a stored procedure as a source of data in Excel.

First, create a connection using the wizard and select any table as a source for the newly created connection. Once the connection is created, choose properties for the connection and switch to the Definition Tab.

Workbook Connections & Connection Properties

In the Definition type, change the Command Type to SQL and as Command text put the EXEC statement to execute the stored procedure. Once you enter, the Exec command, and confirm the dialog, you have fully configured  connection to SQL Server which source is a Stored Procedure.

Excel Connection Properties

On the dialog confirmation a warning message appears telling you, that the connection in Excel and the external connection files doesn’t match and that the link will be removed. Simply confirm the message by Yes as the connection will be stored in the excel workbook.

Confirmation of removing link to a connection file

Such modified connection you can use for importing data to Excel sheet, pivot table, pivot graph etc..