What is a Database Trigger? A SQL trigger is special stored procedure
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.
I recently put together a lesson on how to work with TSQL variables. When you create stored procedures, you can encapsulate logic
We are going to show you the difference between a stored procedure and a user defined function in SQL. This way you
I recently put together a lesson on debugging stored procedures. When you debug stored procedures, you’re able to run their code line
What is a Stored Procedure? A stored procedure is a group of one or more database statements housed in the database’s data
After reading this article you will understand the basics of using the WHILE statement to write a loop within a stored procedure.
After reading this article you will understand what a database cursor is, see an example, and understand when to use them in
After you have read this article, you will get to learn the basics of a dynamic SQL, how to build statements based
After reading this article you will understand the basics of programming a stored procedure using IF ELSE statements; most importantly how keywords,
Using NULLIF to compare missing values to NULL is handy. It become especially useful when you find yourself working with a column
In this post we’ll look at how to use C# with SQL. In this tutorial, we will discuss the process for creating
The SQL @@VERSION global variable returns the version of the currently installed SQL server. It includes the SQL server’s system and the
Learning how to use SQL Variable in queries is a step in the right direction towards making your queries more flexible for
Knowing how to optimize stored procedures is important, so stick around and I’ll show top five stored procedure performance tips you can
In this video we’ll walk you though how to use comments within a query. We’ll go over block as well as inline
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
A reader recently asked about a error converting data type error they received. Since this is a common issue, especially when numeric
We are going to learn how to create a bar chart using SQL server in this puzzle. It can sometimes be fun
In this article, we will be exploring the CAST and CONVERT functions to understand whether there are any significant differences worth knowing
Sort Alphanumeric Values with SQL Server Recently a reader asked me how to sort an alpha numeric field in SQL in natural