In this tutorial, I’m going to discuss one of the most important database objects that you’ll ever come across in SQL Sever Programming, that is Stored Procedures. Stored Procedures are T-sql statements written, pre-compiled and stored in the database which can accept one or more parameters and return one or more parameters. The server stores the execution plan, which is the optimized manner in which all the statements included in the stored procedure can be executed fast and efficient. The query optimizer, which is part of sql server does the job of finding the most optimized way of executing any TSQL statement. In case of stored procedures, the query optimizer first generates the execution plan for the stored procedure at the first time it is executed after creation or after altering the stored procedure and store this execution plan in the database, so that, subsequent executions of the stored procedure will simply use the stored execution plan. This makes the stored procedures faster than running the statements just as a batch every time. There are 4 types of stored procedures. These are System Stored Procedures, User defined Stored Procedures, Extended Stored procedures and CLR (Common Language Runtime) written using the .NET Framework. I’m going to concentrate on User Defined Stored Procedures but lets look at some system stored procedures. The system stored procedures are built-in and they start with SP_###. There are lot of system stored procedures for different purposes. Examples of System stored procedures are
sp_help –Display information about a particular table. like this, sp_help ‘tablename’
sp_helpdb –Display information about a particular database. sp_help ‘dabasename’
To find information about current server processes, use sp_who or sp_who2
sp_rename — used to rename an object , example
sp_rename ‘oldtablename’,’newtablename’ — renamed the table
sp_rename ‘schema.tablename.oldcolumnname’,’newcolumnname’,’column’ — rename a column
Now lets talk about user defined procedures, that is the main point l’m trying to drive home in this tutorial. There are 3 types of User Defined stored procedures, some take parameters, some don’t, some output parameters, some just display information. Here is the syntax for creating a user defined stored procedure in general.
CREATE PROCEDURE<procedurename>(paramters) AS
< any type of tsql statement goes here>
To execute the stored procedure, you use the exec or execute key word followed by the procedure name, like this
EXEC procedurename parameters
Lets look at the different flavours of user defined stored procedures. Lets say we have a table in our database called Employees. Say the table is created like this,
CREATE TABLE Employees(Employeeid int, firstname varchar(50), lastname varchar (50), Dob Date )
1. Stored Procedures with no input
CREATE PROC udp_EmpInfo AS
SELECT * FROM Employees
To execute, do it like this,
EXEC udp_EmpInfo
2. Stored Procedures with input parameters
CREATE PROC udp_EmpInfo (@empid int) AS
SELECT * FROM Employees
Where Employeeid = @empid
To execute, do like this,
EXEC udp_EmpInfo 10
In this case, @empid is a variable and it’s the input parameter for the stored procedure. All the stored procedure does is to list information about the employee whose id matches the input parameter given during the execution. In execution, 10 was passed so the information displayed will be for the employee with id 10.
3. Stored Procedures with Output parameters. Let’s take a look back at the stored procedures above, you’ll realize they are just listing or displaying results. how about if you want to use components of those result sets for further processing, then you’ll need to have a variable that can take those output components separately from the stored procedure, this is where we land in stored procedures with output parameters. Stored procedures can output any type of variable datatype except table variables. Stored Procedures to do not output 2D DATA. Becareful, when l say output 2D data, its different from listing the result sets we saw earlier. l mean stored procedures can not out put table variables. They can however output any other data type. To output table variables, we have to rely on functions, which we’ll be looking at later in the tutorial. So one difference between a function and a stored procedure is that functions can output 2D data or table variables but stored procedures can not. Let’s see how to create stored procedures with output parameters. Lets say, we are given the employee id and we want to use that id and locate that employees date of birth in a variable so that we can use that date of birth stored in variable for whatever we want to do with it.
CREATE PROCEDURE udp_getDob (@empid int, @dateofbirth Date OUT) AS
SELECT @dateofbirth = Dob FROM Employees
WHERE Employeeid = @empid
To execute this stored procedure, we have to declare a variable to receive the date of birth returned from the procedure. We do it like this,
DECLARE @adate DATE
EXEC udp_getDob 10 @adate OUT
PRINT ‘The date of birth is ‘ + Cast(@adate as varchar(50))
This is the walk through, first we declare the procedure by stating it takes @empid as input and its supposed to output @dateofbirth by putting the keyword OUT after the parameter @dateofbirth declaration. In the procedure we use the select statement to assign the DOb of the employee whose id was given to the variable @dateofbirth. Now during execution, we declared a new variable which is of the same data type of the one being outputted by the procedure, which is @adate. To execute we give the input parameter of @empid to be 10 and we gave and output variable to receive the value that the @dateofbirth will return in the procedure. Then we can use this date now to do whatever we want to do with it. Like printing it out or using it for other business requirements. It is worth noting that stored procedures are used to implement the complex business logics. They usually contain lot of statements and can contain functions and even execute other stored procedures within it. Stored procedures can call functions and other stored procedures within them but functions can not call stored procedures within them
4. Stored Procedures with the Return statement.
We see how we can out parameters already but there is another flavour of stored procedures that uses the keyword return to return a single integer value. That means, this stored procedure can only return one integer value. You can’t get anything else using the return statement. Lets see how it’s written.
CREATE PROC udp_Factorial (@Number int) AS
DECLARE @factorial int = 1
IF(@Number > 0)
BEGIN
WHILE(@Number >= 1)
SET @factorial = @factorial * @Number
SET @Number = @Number – 1
END
RETURN @factorial
This simple stored procedure calculates the factorial of a given number and return it. Let see how to execute this type of stored procedure.
DECLARE @fact int
EXEC @fact = udp_Factorial 5
PRINT @fact
So we see how this procedure and its execution differs from the other ones. It’s good to be able to use all the flavours of stored procedures with flexibility and it takes lot of practice to get used to using and understanding all of them.
Last version of stored procedures l’m going to talk about is stored procedures with default input. yeah, l know that feeling, are we done yet? well, as l said earlier, store procedures do lot of work so there are lot of things to be learn about them, but we are almost at the end of what l want to talk about in this tutorial and l think with all this in mind, you can handle anything using stored procedures.
5. Stored Procedures with default input. Assuming you want to write a stored procedure so that, if it’s executed, some of the input parameters can take defaults from the system or given by the user. Let see how we do this, let’s say our store procedure will take the id, name and gender of an employee and insert it into the employee table. By default, we want the gender to be inserted to be M for male, Unless otherwise stated. l’m not being gender biased, l just couldn’t find any quick example of the top of my brain. So let’s do this and forget about the gender fight. Here we go
CREATE PROC udp_insert(@empid int, @name varchar(50), @gender varchar(1) =’M’) AS
INSERT INTO Employees
VALUES(@empid,@name,@gender)
Lets execute it now,
EXEC udp_insert 1, ‘Vincent’
the above will by default insert M in the gender column for Vincent. Let’s now give it a value.
EXEC udp_insert 2, ‘Gabi’ ,’F’
IF you have more than one default input parameters, then it’s good to specify the value for those that you are providing specifically at run time so that the system don’t get confused. For example, if there is another column for hire date and you want to make that hire date by default some date. Then you have two default input parameters so if you are leaving out some and providing the other, you have to specify the one you are providing by mentioning its name in the execution. like this
EXEC udp_insert 2,’Gabi’,@gender =’S’
That will tell the system that, gender default input parameter has been overridden so any non supplied input parameter will take the default value of the second default input parameter.
That’s is it for now, in the next tutorial, I’ll talk about advantages of stored procedures and how to recompile stored procedures. Keep in touch, Peace.
