SQL Programming
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.
Latest Posts
-
What is a SQL Trigger?
What is a Database Trigger? A SQL trigger is special stored procedure that is run when specific actions occur within a database. Most database triggers are defined to run when changes are made to a table’s data. Triggers can be defined to run instead of or after DML (Data Manipulation Language) actions such as INSERT,…
-
TSQL Variables in Stored Procedures
I recently put together a lesson on how to work with TSQL variables. When you create stored procedures, you can encapsulate logic securely, and part of this process is using TSQL variables to temporarily store and manipulate values. After watching this video you’ll be able to declare TSQL variables and understand how they are used…
-
User Defined Function versus Stored Procedure
We are going to show you the difference between a stored procedure and a user defined function in SQL. This way you can get on the right path in starting your scripting skills. This article is based on my video What’s the Difference Between a Stored Procedure and User Defined Function? Is there a difference…
-
Debugging Stored Procedures in SQL Server
I recently put together a lesson on debugging stored procedures. When you debug stored procedures, you’re able to run their code line by line, to easily troubleshoot logic issues. If you’re not familiar with debugging stored procedures you’ll want to watch this introductory video; as it will get you acquainted with some of the main…
-
Stored Procedures: the ultimate guide.
What is a Stored Procedure? A stored procedure is a group of one or more database statements housed in the database’s data dictionary and called from either a remote program, another stored procedure, or the command line. We commonly call them SPROCS, or SP’s. Stored procedure features and command syntax are specific to the database…
-
WHILE Statement – Using in SQL Server
After reading this article you will understand the basics of using the WHILE statement to write a loop within a stored procedure. 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…
-
What is a Database Cursor?
After reading this article you will understand what a database cursor is, see an example, and understand when to use them in a stored procedure. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using…
-
Dynamic SQL – Build using Stored Procedures
After you have read this article, you will get to learn the basics of a dynamic SQL, how to build statements based on variable values, and how to execute constructed statements using sp_executesql and EXECUTE() from within a stored procedure. All examples found in this lesson are based on the Microsoft SQL Server Management Studio…
-
Use IF ELSE Statements to Program a Stored Procedure
After reading this article you will understand the basics of programming a stored procedure using IF ELSE statements; most importantly how keywords, such as BEGIN, END, IF and ELSE affect the order of execution within your stored procedures. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample…
-
Using NULLIF with Missing Values
Using NULLIF to compare missing values to NULL is handy. It become especially useful when you find yourself working with a column contains both empty and NULL text. NULLIF provide handy and compact way to compare both types of data to NULL itself. If you like what you are seeing, then why not get some…
-
How to use C# with SQL
In this post we’ll look at how to use C# with SQL. In this tutorial, we will discuss the process for creating a very simple C# console application that connects to a SQL Server database. You will learn the most basic steps needed to use C# with SQL. You will see how we can gather…
-
SQL @@VERSION (Transact SQL)
The SQL @@VERSION global variable returns the version of the currently installed SQL server. It includes the SQL server’s system and the build information. Description All the details required for upgrade and installations of new features in SQL Server are provided by the @@VERSION function. The information includes SQL Server version, processor architecture, SQL Server…
-
How to Use SQL Variables in Queries
Learning how to use SQL Variable in queries is a step in the right direction towards making your queries more flexible for you and others to use. Why “hardcode” when you can parameterize? In this video and the following article, we’ll take you step-by-step through the process of changing a query’s filter criteria into a…
-
Top 5 Stored Procedure Performance Tips You Can Use
Knowing how to optimize stored procedures is important, so stick around and I’ll show top five stored procedure performance tips you can use today. Hey, this is Kris from EssentialSQL. Let’s dig into some super easy tips you can use to speed up your stored procedures. Before we begin, let me point out that the…
-
Use SQL Comments to Code Like a Boss
In this video we’ll walk you though how to use comments within a query. We’ll go over block as well as inline comments. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have watched the video check out the sample…
-
SQL BIT Data Type
Use the SQL BIT data type to define columns, variables, and parameters value of 1, 0, or NULL. Given their Yes/No nature, designer us the BIT type with flag and indicator columns: Converting BIT Values The string values TRUE and FALSE convert to BIT: TRUE is converted to 1 and FALSE to 0. Converting any non-zero value promotes BIT to 1. Examples Here is SQL…
-
How do I handle a “Error converting data type” error?
A reader recently asked about a error converting data type error they received. Since this is a common issue, especially when numeric data is stored withing VARCHAR datatypes, I thought you would appreciate the answer I shared with them. I’m using Windows 10 and SQL SMS 2014. When I run a query, I get the…
-
How to Create A Bar Chart Using SQL Server
We are going to learn how to create a bar chart using SQL server in this puzzle. It can sometimes be fun to see what you can do with the SELECT statement. Besides, I figured it would be fun to see if I could create an “old-fashioned” bar chart — this is much like what we used to…
-
What is the Difference between Cast versus Convert?
In this article, we will be exploring the CAST and CONVERT functions to understand whether there are any significant differences worth knowing when we want to data from one type to another. Since the CAST and CONVERT can be used interchangeably in most situations, you may wonder if one function is better than the other.…
-
Use SQL Server to Sort Alphanumeric Values
Sort Alphanumeric Values with SQL Server Recently a reader asked me how to sort an alpha numeric field in SQL in natural order. Alphanumeric values are commonly found and don’t sort naturally using numeric methods. When we have a numeric fields we expect values to be sorted in ordinal order, for example: 1,2,5,7,10,11,15,20,21 However when…
