T-SQL 101: 125 Executing Stored Procedures in SQL Server Using T-SQL

In T-SQL, the way we execute a stored procedure is to use the EXECUTE statement. We can write it as EXECUTE or as EXEC. You’ll almost always see it written as EXEC.

In the first example above, I’ve executed a stored procedure called dbo.GetCinemaChanges. Note that I’ve included the dbo schema in the name of the procedure. For procedures like this, you should always include the schema name.

The procedure also takes one parameter called @CurrentDate and set it to the 28th February 2012.

System Stored Procedures

The procedure shown in the first example is a user-defined stored procedure. There are also system stored procedures that are included with SQL Server. The next two examples are both system stored procedures.

You can see the available system stored procedures when you have Object Explorer in SQL Server Management Studio connected to any database.

If you look at the list of those in Object Explorer, you’ll see that most have an sp_ prefix.

You should never use this prefix on your own stored procedures.

Avoid Prefixes

If you scroll to the bottom of this list, you’ll also see a few entries with an xp_ prefix. This is a good example of why having these prefixes isn’t a good idea.

The xp_ prefix was originally used for Extended Stored Procedures. These were procedures that were usually written in C++ (and installed from a DLL) rather than in T-SQL, and were a special type of procedure that executed within the memory space of SQL Server. They are deprecated and shouldn’t be used any more.

However, at some point in time, these particular procedures changed from being part of a DLL, and were now written in T-SQL. So they should have had an sp_ prefix, not an xp_ prefix.

But because of the number of people already using them, Microsoft wasn’t prepared to change the name of them. And so we now have system stored procedures with an xp_ prefix.

No big surprise that this also happened the other way around. There are a number of extended stored procedures that have an sp_ prefix. You can see the extended stored procedures in the master database.

The use of a prefix has turned into quite a mess. Prefixes like this are best avoided.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-03-06