Developing T-SQL Stored Procedures to Deal with Lookup Tables

As I wanted to demonstrate stored procedures which do not modify existing data in AdventureWorks/Northwind, I created a table “emp” with the following columns and data types:

• Empno (int)
• Ename (nvarchar(50))
• Sal (float)
• Deptno (int)

I pushed the following sample rows into that table:

How to insert a row into a table using a T-SQL stored procedure

The following is the stored procedure which deals with inserting a particular row into a table. 

CREATEPROCEDURE [dbo].[p_emp_Insert]
      @empno int,
      @ename varchar(50),
      @sal float,
      @deptno int
AS
INSERTINTO [dbo].[emp] (
      [empno],
      [ename],
      [sal],
      [deptno]
)VALUES (
      @empno,
      @ename,
      @sal,
      @deptno
)
RETURN

The above is a stored procedure named “p_emp_insert” which accepts four parameters (according to the columns available in the table).  Once all the parameters are passed in, it executes an INSERT statement to insert those values in the form of a row.

To test the above stored procedure, you can issue the following statement in Query Analyzer or SQL Server Management Studio.

executedbo.p_emp_Insert 2001, 'zzz', 4500, 20

The next section will give the code for updating a row.

How to update/delete a row in a table using a T-SQL stored procedure

The following is the stored procedure which deals with updating a particular row in a table. 

CREATEPROCEDURE [dbo].[p_emp_Update]
      @empno int,
      @ename varchar(50),
      @sal float,
      @deptno int
AS
UPDATE[dbo].[emp] SET
      [ename] = @ename,
      [sal] = @sal,
      [deptno] = @deptno
WHERE
      [empno] = @empno
RETURN

The above is a stored procedure named “p_emp_update” which accepts four parameters (according to the columns available in the table).  Once all the parameters are passed in, it executes an UPDATE statement to replace the existing values in a row identified by the given employee number.

To test the above stored procedure, you can issue the following statement in Query Analyzer or SQL Server Management Studio.

executedbo.p_emp_Update 2001, 'bbb', 5600, 10

Once you execute the above statement, it will automatically modify the information of employee identified with the employee number 2001. Deleting a row is very similar to the above; it could be written something like this:

CREATEPROCEDURE [dbo].[p_emp_Delete]
      @empno int
AS
DELETEFROM [dbo].[emp]
WHERE
      [empno] = @empno
RETURN

You can observe that to delete a row (or employee), the employee number alone is enough. That is the reason I have only one parameter to pass to the above stored procedure. To execute the above store procedure, you can give a command something like the following:

executedbo.p_emp_Delete 2001

How to insert/update using a single T-SQL stored procedure

Let us consider that you wanted to insert a row, if it's not available, or update a row, if it is available, using a single stored procedure. The following is the stored procedure which deals with this:

CREATEPROCEDURE [dbo].[p_emp_save]
      @empno int,
      @ename varchar(50),
      @sal float,
      @deptno int
AS
IFEXISTS(SELECT [empno] FROM [dbo].[emp] WHERE [empno] = @empno)
BEGIN
      UPDATE [dbo].[emp] SET
            [ename] = @ename,
            [sal] = @sal,
            [deptno] = @deptno
      WHERE
            [empno] = @empno
END
ELSE
BEGIN
      INSERT INTO [dbo].[emp] (
            [empno],
            [ename],
            [sal],
            [deptno]
      ) VALUES (
            @empno,
            @ename,
            @sal,
            @deptno
END
RETURN

The above is a stored procedure named “p_emp_save” which accepts four parameters (according to the columns available in the table). Once all the parameters are passed in, it executes an INSERT statement if the given employee number does not exist within the table. It executes an UPDATE statement if a row exists with the given employee number.

To test the above stored procedure, you can issue the following statement in Query Analyzer or SQL Server Management Studio.

executedbo.p_emp_save 2002, 'aaa', 4500, 20

Once you execute the above, it will insert a row with employee number 2002 (if it doesn’t exist).

executedbo.p_emp_save 2002, 'bbb', 5400, 10

Once you execute the above, just after the previous one, you will see that the values are updated instead of inserted.

How to retrieve one or all rows in a table using a T-SQL stored procedure

Let us consider that we would like to retrieve all the details of a particular employee based on the given employee number. The following would be the stored procedure to use for retrieving the same.

CREATEPROCEDURE [dbo].[p_emp_details]
      @empno int
AS
SETTRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
      [empno],
      [ename],
      [sal],
      [deptno]
FROM
      [dbo].[emp]
WHERE
      [empno] = @empno
RETURN

If we wanted to retrieve all rows, we might use the following stored procedure:

CREATEPROCEDURE [dbo].[p_emp_list]
AS
SETTRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
      [empno],
      [ename],
      [sal],
      [deptno]
FROM
      [dbo].[emp]
RETURN

How to retrieve a set of rows in a table, using a custom condition in a T-SQL stored procedure

Let us consider that you want to retrieve a set of rows based on an unknown WHERE condition. That means the WHERE condition should be made dynamic (using a parameter). The following is an example for the same:

CREATEPROCEDURE [dbo].[p_emp_listByCriteria]
      @WhereCondition nvarchar(500)
AS
DECLARE@SQL nvarchar(3250)
SET@SQL = '
SELECT * FROM
      [dbo].[emp]
WHERE
      '+ @WhereCondition
EXECsp_executesql @SQL
RETURN

From the above, you can observe that I am executing the SELECT statement dynamically using “sp_executesql,” which is a built-in. To test the above stored procedure, try to execute the following statement, which should return to you all the employees belonging to department 10:

executep_emp_listByCriteria 'deptno = 10'

You can further enhance the above stored procedure so that it will work even when not providing any condition. The following would be the code for this:

CREATEPROCEDURE [dbo].[p_emp_listByCriteria]
      @WhereCondition nvarchar(500) = null
AS
DECLARE@SQL nvarchar(3250)
IF@WhereCondition is NULL 
      BEGIN
      SET @SQL = 'SELECT * FROM [dbo].[emp]'
      END
ELSE
      BEGIN 
      SET @SQL = 'SELECT * FROM [dbo].[emp] WHERE ' +
@WhereCondition
      END
EXECsp_executesql @SQL
RETURN

The above code accepts a default parameter. If the parameter is provided with a value (or WHERE condition), it will execute SELECT with WHERE. If the parameter is not provided (or NULL), it would execute SELECT without any WHERE.

How to retrieve a set of rows in a particular order using a T-SQL stored procedure

Sometimes, we may need a set of rows based on a particular order, and that too may mean different columns. The following is the stored procedure to help you achieve the same:

CREATEPROCEDURE [dbo].[p_emp_Sortedlist]
      @OrderByExpression nvarchar(250) = NULL
AS
SETTRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE@SQL nvarchar(3250)
SET@SQL = 'SELECT [empno], [ename], [sal], [deptno]
FROM [dbo].[emp] ' 
IF@OrderByExpression IS NOT NULL
BEGIN
      SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END
EXECsp_executesql @SQL
RETURN

Sometimes, we may need to filter and order the rows based on user customization. The following is the code you need to do the same:

CREATEPROCEDURE [dbo].[p_SelectempsDynamic]
      @WhereCondition nvarchar(500) = NULL,
      @OrderByExpression nvarchar(250) = NULL
AS
SETTRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE@SQL nvarchar(3250)
IF@WhereCondition is NULL 
      BEGIN
      SET @SQL = 'SELECT * FROM [dbo].[emp] '
      END
ELSE
      BEGIN 
      SET @SQL = 'SELECT * FROM [dbo].[emp] WHERE ' +
@WhereCondition
      END
IF@OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
      SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END
EXECsp_executesql @SQL
RETURN

How to check whether a row exists in table or not using a T-SQL stored procedure

Sometimes, we may need to just check whether a row exists in a table or not using a stored procedure. The following is the stored procedure for dealing with the same:

CREATEPROCEDURE [dbo].[p_emp_isExists]
      @empno int
AS
IFexists (SELECT [empno] FROM [dbo].[emp] WHERE [empno] =  +
@empno)
      BEGIN
      RETURN 1
      END
ELSE
      BEGIN
      RETURN 0
      END

The above returns 1 if the employee exists or 0 if it does not exist. In the above stored procedure, I am using a RETURN statement to return back to the calling program. You can also achieve the same without using a RETURN statement using the following:

CREATEPROCEDURE [dbo].[p_emp_isExists]
      @empno int,
      @isExists bit OUT
AS
IFexists (SELECT [empno] FROM [dbo].[emp] WHERE [empno] =  +
@empno)
      BEGIN
      SET @isExists = 1
      END
ELSE
      BEGIN
      SET @isExists = 0
      END

Instead of using a RETURN statement, I used an OUTPUT parameter to return the value to the calling program. It all depends on the necessity and use of stored procedures in our applications.

How to retrieve a set of rows page by page using a T-SQL stored procedure

If you need to retrieve a set of rows on a page-by-page basis, you may need to consider the following stored procedure:

CREATEPROCEDURE dbo.db_emp_ListByPage
@CurrentPage As tinyint,
@PageSize As tinyint
AS
Declare@FirstRec int
Declare@LastRec int
Set@FirstRec = (@CurrentPage - 1) * @PageSize
Set@LastRec = (@CurrentPage * @PageSize + 1)
CREATETABLE #empTemp
(
AutoId int IDENTITY PRIMARY KEY,
empno int,
ename nvarchar(50),
sal   float,
deptno int
)
INSERTINTO #empTemp
(
empno,ename,sal,deptno
)
SELECT
empno,ename,sal,deptno
FROM emp
SELECT
empno, ename, sal, deptno
FROM  #empTemp
WHERE
AutoID > @FirstRec
AndAutoID < @LastRec

The above code uses a temporary table in the database to page the rows. For huge tables, the above method may not work well. And there exists several efficient ways to deal with the paging of rows using stored procedures. I shall dedicate one article exclusively to the paging of rows using T-SQL stored procedures.

Any feedback, suggestions, bugs, errors, improvements etc., are highly appreciated at jag_chat@yahoo.com.