Creating stored procedures and additional objects in SQL Server Database Project

In the previous post Creating a new SQL Server Database Project we learned how to create SQL Database project, now we are going to show how to work with some other SQL objects like stored procedures, indexes, key, etc.

Adding stored procedure to the project

When you create stored procedure, you have to keep in mind that you have to put your stored procedure in the appropriate folder related to the particular application domain. In your example, we will create stored procedure in 'Application' domain. So at first, let's create folder 'Stored Procedures' in 'Application' folder. After that right-click on created folder → Add → Stored Procedure.

CreateRoleIfNonexistent.sql stored procedure code

CREATE PROCEDURE [Application].CreateRoleIfNonexistent
@RoleName sysname
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @RoleName AND type = N'R')
    BEGIN
        BEGIN TRY

            DECLARE @SQL nvarchar(max) = N'CREATE ROLE ' + QUOTENAME(@RoleName) + N';'
            EXECUTE (@SQL);

            PRINT N'Role ' + @RoleName + N' created';

        END TRY
        BEGIN CATCH
            PRINT N'Unable to create role ' + @RoleName;
            THROW;
        END CATCH;
    END;
END;

Now we have learned now to create stored procedure in SQL Server Database project.

How to add other SQL objects

Select appropriate folder → right-click on it → Add → New Item.

There are several groups of items:

• Programmability
• Security
• Service Broker
• Server Objects
• SQL CLR
• SQL CLR C#
• Storage
• Table and Views
• User Scripts

If you do not like to search for each group, there is search textbox in the right top corner, and you can use it to find necessary item. Code example for this tutorial could be found using following link https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/02-Add-Stored-Procedure.