Developing T-SQL Stored Procedures to Deal with Lookup Tables - How to retrieve a set of rows in a particular order using a T-SQL stored procedure

(Page 4 of 5 )

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.

Next: How to retrieve a set of rows page by page 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!