This is the fourth post in a series named One Way to Build an SSMS Database Solution. Please find additional posts:

1. One Way to Build an SSMS Database Solution – Introduction
2. One Way to Build an SSMS Database Solution – Add a New Query
3. One Way to Build an SSMS Database Solution – Add a Table and a View
4. One Way to Build an SSMS Database Solution – Add a Stored Procedure and the Build

Create a Stored Procedure Named I in the S Schema

Click the “New Query” button in SSMS and add the following T-SQL:

Once added, the T-SQL should appear similar to:

Note the DDL to manage stored procedures is very similar to the DDL for managing views.

If all goes according to plan, the first execution of the s.i DDL T-SQL statement should generate the following messages:

Subsequent executions should generate the following messages:

Save the DDL T-SQL file, and then drag it into the Solution Explorer’s Queries virtual folder:

The demo scripts for database artifacts are now complete. The next step is to execute the database artifact scripts in the proper order. For that, I prefer to make use of another lesser-known feature of SSMS: SQLCMD mode.

Use SQLCMD Mode to Execute Child Scripts

Click the “New Query” button in SSMS and add the following T-SQL:

This script relies on SQLCMD mode, which you set by first selecting an SSMS script. Next, click the Query dropdown menu and then select SQLCMD Mode:

The first two lines of this script – along with several others – are highlighted with a gray background when SQLCMD mode is switched on:

These lines are SQLCMD commands. The gray background indicates SSMS recognizes each line as such.

The first line  – :setvar ScriptPath "E:\Projects\DemoDB\DemoDB\" – creates and initializes a SQLCMD variable named ScriptPath, which is initialized to the directory that contains the “child” scripts for this project – the very scripts we’ve been developing to create and then configure the DemoDB database.

The second line – :on error exit – configures SQLCMD to stop executing the script if an error is encountered.

The next few lines are print statements that indicate when, where, by whom, and the location of the database project deployment:

Executing “Child” Scripts

The next section of the build script executes “child” scripts. Each call to execute a child script follows the format:

/* Execute [script file name] */
print 'SQLCmd: Calling $(ScriptPath)[script file name]'
:r $(ScriptPath)[script file name]

The first line above is a comment. Comments are helpful. Leave some. Future you will thank you!
The second line is instrumentation that communicates the intent to call a child script.
The third line is the actual SQLCMD statement – “run” – that executes the child script found at the path – $(ScriptPath)[script file name] – passed as an argument. The $(ScriptPath)e name] SQLCMD variable is defined at the top of the script.

We’ve defined five child scripts. In the Build script, they appear as shown here:

The final portion of the Build script is instrumentation to let the script executor know the deployment script completed:

The first execution of the Build script delivers the following messages to the executioner:

Subsequent executions yield the following instrumented output:

Conclusion

In this post we examined creating a script to generate a stored procedure and another script to tie the Build operation together.

It is very possible, as you read this conclusion, that you are thinking, “Andy? This is way more complex than a Visual Studio database project!” I know how you feel. I do! I learned how to use SSMS solutions back in the day, and I feel the same way about VS database projects.

I promise I am not recommending you abandon VS database projects if they work well for you and your team. I offer SSMS solutions simply as an alternative to anyone who – like me – struggles with Visual Studio database projects.