Solved

Advanced WHERE clause in Stored Procedure for SQL Server 2005.

Posted on 2009-03-31
1
212 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now