What is SQL Programming? Does it differ from writing queries?
To me it does.
In my mind SQL Programming are the things you do to automate your queries. You can do this either by writing scripts and running them interactively or by creating stored procedures with run “in the background.”
Either way, once you start programming SQL you see there you queries become more powerful, as you’re able to introduce some interesting concepts, such as variables and error trapping.
If you’re looking to learn more about scripting or stored procedures, then check out Stored Procedures: the ultimate guide. It’s a great introduction to use of flow of control statement such as IF/THEN and WHILE loops.
For those programming C#, check out How to Use C# with SQL to see how we pull together all the dev tools and correct connection object to get you up and submitting SQL commands to the DBMS.
Also, if you’re just starting out learning SQL, I recommend reading Use SQL Comments to Code Like a Boss. You may think comments are silly, but trust me, they are valuable, and set the pros apart from the amateurs. Hint: The pros use them!
Once you’re comfortable writing queries, try writing some Dynamic SQL. That’s a SQL statement you create on-the-fly and execute at run-time. That means, you don’t have to “hard code” the query beforehand. It very powerful technique to get to know.
A stored procedure is a group of one or more database statements stored in the database’s data dictionary. They can be called from T-SQL using an execute command that can be called from a program outside the database server such as a web-server or a client program.
They are a great way to encapsulate logic securely, while allowing client program an easy way to execute it. The good news is that writing SQL stored procedures isn’t as hard as everyone makes them out to be.
If you’re new to them, then check out our Ultimate Guide to Stored Procedures.
There are three main aspects to stored procedures:
- Their definition, including input and output parameters.
- Control of flow statements, such as WHILE, dictate the order the database executes SQL commands.
- SQL used to retrieve and change data.
If you want to dig deeper, the I would recommend these popular articles:
For more advanced work, check out our popular article on How to Build Dynamic SQL.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. Start learning SQL today using these free tools with my guide Getting Started Using SQL Server.