Developing T-SQL Stored Procedures to Deal with Lookup Tables - How to retrieve one or all rows in a table using a T-SQL stored procedure

(Page 3 of 5 )

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.

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

More ASP.NET Articles
More By Jagadish Chaterjee

Comments On: Developing T-SQL Stored Procedures to Deal with Lookup Tables

· Hello guys! this is a simple article on developing most frequently used T-SQL stored...   >>> Post your comment now!