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:
- Service Broker
- Server Objects
- SQL CLR
- SQL CLR C#
- 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.