Solved

Advanced WHERE clause in Stored Procedure for SQL Server 2005.

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
A short film showing how OnPage and Connectwise integration works.

911 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

23 Experts available now in Live!

Get 1:1 Help Now