Solved

Advanced WHERE clause in Stored Procedure for SQL Server 2005.

Posted on 2009-03-31
1
243 Views
Last Modified: 2012-05-06

Hi expert!

Im using SQL Server 2005 and have question. I have follow SP

CREATE PROCEDURE [dbo].[New_ForTest]
@PlayListID      bigint,
@TypeOrder      smallint
 AS

IF @TypeOrder=1
   SELECT *  FROM  dbo.Events ORDER BY EventID
ELSE
   SELECT *  FROM  dbo.Events ORDER BY ModuleID,EventID

It is about this @TypeOrder. Is there other short way to produce same result ? SOmethink like:

DECLARE @Order
IF @TypeOrder=1
  SET @Order=" ORDER BY EventID"
ELSE
 SET @Order=" ORDER BY ModuleID,EventID"


And then
SELECT *  FROM  dbo.Events + @Order

I need this because order type can be 5-6 ways and I do not want to have different SELECT FOR every of this order by , if I need to change this SQL I will need to do this in all 5-6 SQL which is foolishly ...
0
Comment
Question by:dvplayltd
1 Comment
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 24028390
You could use a CASE statement in the ORDER BY clause.  

SELECT *
FROM dbo.Events
ORDER BY CASE WHEN @TypeOrder <> 1 THEN ModuleID
              END, EventID

Greg


0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question