Is there a better way to write this??

[sqlcasey]

IF @sortorder = '1'
do this....

IF @sortorder = '2'
do this....

IF @sortorder = '3'
do this....

IF @sortorder = '4'
do this....

IF @sortorder = '5'
do this....


You get the picture!

Thanks

 

[mrdenny]

What are you trying to get done here? Your sample doesn't explain what you what done.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ksbigfoot]

Are you looking to use a case statement?

 

[sqlcasey]

Is a case stmt faster?

The "do this" part is a simple select statement, however the WHERE clause differs for each @sortorder type, otherwise the select statements are identical.

Thanks

 

[mrdenny]

Using this method you can't cache the execution plans
Something like this will cache the execution plan and should therefor get you better performance.

select *
from table
where (@SortOrder=1 and Col1='Value') or (@SortOrder=2 and Col3='test') or (@SortOrder=3 and Col7='Test2') or (@SortOrder=4 and Col4 = 'SomethingElse')

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

Ah ha...

Take a look at this code.

Declare @Temp Table (Id Integer, Name VarChar(20))

Insert Into @Temp Values(1, 'George')
Insert Into @Temp Values(2, 'sqlDenis')
Insert Into @Temp Values(3, 'sqlCasey')
Insert Into @Temp Values(4, 'AlexCuse')

Declare @SortOrder VarChar(1)

Set @SortOrder = '[!]2[/!]'

Select Id,
       Name
From   @Temp
Order By Case When @SortOrder = '1' Then Convert(VarChar(10), Right(Replicate('0', 10) + Convert(VarChar(10), Id), 10))
              When @SortOrder = '2' Then Name
         End

Copy paste this to query analyzer. It uses a table variable, so it won't have any affect on whatever database you have open.

Then, run the query. You'll notice that the output is sorted by column 2 (Name). Then, change the part in red to 1 and re-run the query. Now the data is sorted by id.

There's a bit of weirdness with the first WHEN statement.

[tt][/blue]
Convert(VarChar(10), Right(Replicate('0', 10) + Convert(VarChar(10), Id), 10))
[/blue][/tt]

This will convert integers to strings with zero padding on the left (so that numbers will sort properly). This is necessary because each part of the case statement MUST return the same data type.

From a performance perspective, this may not be any better than what you are already doing, however, from a coding perspective, it will simply the procedure.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DocuMan]

CASE only works when you want to return different values based on a variable or condition.

If you want to execute differing lines of code based on a variable then

If v = 1
begin
------
end

Is the way to go. It looks clunky and performance may suffer, but it's easily readable by humans.

 

[sqlcasey]

Hi George,

I was wondering if you could take a look at this and tell me what's wrong here:

CREATE PROCEDURE [dbo].[IL_ExportHomes_Select_ByCustId_kl]
	@CustId int,
	@SortOrder varchar(100)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT h.homeid, h.hometitle, h.homelocation, t.hometype, 
			h.homeprice, h.homeactive, h.homeopen, h.homeorder, 
			e.LiveDealFeature 
			FROM homes h , hometypes t, exporthomes e 
			WHERE h.hometypeid = t.hometypeid AND h.homeid = e.homeid 
			AND h.custid = @CustId AND h.homeactive <> 0 
	ORDER BY CASE
			WHEN @SortOrder = '' THEN h.homeprice desc, h.homeorder
			WHEN @SortOrder = 'e.LiveDealFeature desc' THEN e.LiveDealFeature desc
			WHEN @SortOrder = 'e.LiveDealFeature' THEN e.LiveDealFeature
			WHEN @SortOrder = 'h.homeTitle desc' THEN h.homeTitle desc
			WHEN @SortOrder = 'h.homeTitle' THEN h.homeTitle
			WHEN @SortOrder = 'h.homeLocation desc' THEN h.homeLocation desc
			WHEN @SortOrder = 'h.homeLocation' THEN h.homeLocation
			WHEN @SortOrder = 't.homeType desc' THEN t.homeType desc
			WHEN @SortOrder = 't.homeType' THEN t.homeType
			WHEN @SortOrder = 'h.homePrice desc' THEN h.homePrice desc
			WHEN @SortOrder = 'h.homePrice' THEN h.homePrice
			WHEN @SortOrder = 'h.homeOpen desc' THEN h.homeOpen desc
			WHEN @SortOrder = 'h.homeOpen' THEN h.homeOpen
	END

END

The error is:

Msg 156, Level 15, State 1, Procedure IL_ExportHomes_Select_ByCustId_kl, Line 15
Incorrect syntax near the keyword 'desc'.

 

[ksbigfoot]

hey sqlcasey,

I ran into this problem to where you have

WHEN @SortOrder = 'e.LiveDealFeature desc' THEN e.LiveDealFeature desc

If you take out the desc, it works.

So what I did is I passed into another parameter and did an if statement.
I wrote up some code, but can't guarantee that syntax is correct, but will point you in the right direction.

CREATE PROCEDURE [dbo].[IL_ExportHomes_Select_ByCustId_kl]
    @CustId int,
    @SortOrder varchar(100),
    @AscDesc int
AS
if @AscDesc = 1
Begin
SELECT h.homeid, h.hometitle, h.homelocation, t.hometype, 
            h.homeprice, h.homeactive, h.homeopen, h.homeorder, 
            e.LiveDealFeature 
            FROM homes h , hometypes t, exporthomes e 
            WHERE h.hometypeid = t.hometypeid AND h.homeid = e.homeid 
            AND h.custid = @CustId AND h.homeactive <> 0 
    ORDER BY CASE
            WHEN @SortOrder = 'e.LiveDealFeature' THEN e.LiveDealFeature
            WHEN @SortOrder = 'h.homeTitle' THEN h.homeTitle
            WHEN @SortOrder = 'h.homeLocation' THEN h.homeLocation
            WHEN @SortOrder = 't.homeType' THEN t.homeType
            WHEN @SortOrder = 'h.homePrice' THEN h.homePrice
            WHEN @SortOrder = 'h.homeOpen' THEN h.homeOpen
	END
Desc

End
Else
Begin
SELECT h.homeid, h.hometitle, h.homelocation, t.hometype, 
            h.homeprice, h.homeactive, h.homeopen, h.homeorder, 
            e.LiveDealFeature 
            FROM homes h , hometypes t, exporthomes e 
            WHERE h.hometypeid = t.hometypeid AND h.homeid = e.homeid 
            AND h.custid = @CustId AND h.homeactive <> 0 
    ORDER BY CASE
            WHEN @SortOrder = 'e.LiveDealFeature' THEN e.LiveDealFeature
            WHEN @SortOrder = 'h.homeTitle' THEN h.homeTitle
            WHEN @SortOrder = 'h.homeLocation' THEN h.homeLocation
            WHEN @SortOrder = 't.homeType' THEN t.homeType
            WHEN @SortOrder = 'h.homePrice' THEN h.homePrice
            WHEN @SortOrder = 'h.homeOpen' THEN h.homeOpen
	END
Asc
End

 

[ksbigfoot]

Now that I read what I posted, might be able to put an if statement for just the Desc and Asc.

 

[sqlcasey]

Hi, your first post makes sense... not sure what you mean by the next post:

Now that I read what I posted, might be able to put an if statement for just the Desc and Asc.

I will try your first suggestion however!

THANKS

 

[AlexCuse]

Hmm,

I have tested this a few times using George's query, and ksbigfoot is on the right track.

However, this order by clause
'h.homeprice desc, h.homeorder'
is going to be extra tricky. You may need to define all of your order by clauses as varchar strings and reference them that way?

I will think about this some more in the morning.

Good Luck,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[sqlcasey]

Hi,

This is how I wrote it:

IL_ExportHomes_Select_ByCustId_kl 570, 'h.homeTitle desc'

alter PROCEDURE [dbo].[IL_ExportHomes_Select_ByCustId_kl]
	@CustId int,
	@SortOrder varchar(100)
AS
BEGIN
	SET NOCOUNT ON;

	IF @SortOrder = ''
		BEGIN

			SELECT h.homeid, h.hometitle, h.homelocation, t.hometype, 
					h.homeprice, h.homeactive, h.homeopen, h.homeorder, 
					e.LiveDealFeature 
					FROM homes h , hometypes t, exporthomes e 
					WHERE h.hometypeid = t.hometypeid AND h.homeid = e.homeid 
					AND h.custid = @CustId AND h.homeactive <> 0 
					ORDER BY h.homeprice desc, h.homeorder
		END

	ELSE IF @SortOrder LIKE '%desc%'
		BEGIN

			SELECT	h.homeid, h.hometitle, h.homelocation, t.hometype, 
					h.homeprice, h.homeactive, h.homeopen, h.homeorder, 
					e.LiveDealFeature 
					FROM homes h , hometypes t, exporthomes e 
					WHERE h.hometypeid = t.hometypeid AND h.homeid = e.homeid 
					AND h.custid = @CustId AND h.homeactive <> 0 
			ORDER BY CASE
					WHEN @SortOrder = 'e.LiveDealFeature desc' THEN e.LiveDealFeature
					WHEN @SortOrder = 'h.homeTitle desc' THEN h.homeTitle
					WHEN @SortOrder = 'h.homeLocation desc' THEN h.homeLocation
					WHEN @SortOrder = 't.homeType desc' THEN t.homeType
					WHEN @SortOrder = 'h.homePrice desc' THEN h.homePrice
					WHEN @SortOrder = 'h.homeOpen desc' THEN h.homeOpen
			END
			DESC -- descending order
		END
	ELSE
		BEGIN
			SELECT h.homeid, h.hometitle, h.homelocation, t.hometype, 
						h.homeprice, h.homeactive, h.homeopen, h.homeorder, 
						e.LiveDealFeature 
						FROM homes h , hometypes t, exporthomes e 
						WHERE h.hometypeid = t.hometypeid AND h.homeid = e.homeid 
						AND h.custid = @CustId AND h.homeactive <> 0 
				ORDER BY CASE
					WHEN @SortOrder = 'e.LiveDealFeature'		THEN e.LiveDealFeature
					WHEN @SortOrder = 'h.homeTitle'				THEN h.homeTitle
					WHEN @SortOrder = 'h.homeLocation'			THEN h.homeLocation
					WHEN @SortOrder = 't.homeType'				THEN t.homeType
					WHEN @SortOrder = 'h.homePrice'				THEN h.homePrice
					WHEN @SortOrder = 'h.homeOpen'				THEN h.homeOpen
				END
				ASC -- ascending order
		END		
END

But I get this error when I use @SortOrder = 'h.homeTitle desc':

Msg 235, Level 16, State 0, Procedure IL_ExportHomes_Select_ByCustId_kl, Line 23
Cannot convert a char value to money. The char value has incorrect syntax.

It works ok on the int fields.

So close... yet far

Any suggestions??

Thanks

 

[AlexCuse]

Hmmm. Any chance you have something in that column that is not money?

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[gmmastros]

All of the fields returned in the order by should have the same data type (preferably varchar because it's easy to convert to that).

I don't know what the data types are for the following fields... e.LiveDealFeature, h.homeTitle, h.homeLocation, t.homeType, h.homePrice, h.homeOpen

I assum that homePrice is the Money data type, so I recommend you change...

WHEN @SortOrder = 'h.homePrice' THEN h.homePrice

To

[tt][blue]
WHEN @SortOrder = 'h.homePrice' THEN Right('000000000000000000' + Convert(VarChar(20), h.homePrice), 18)
[/blue][/tt]

This will put zero's in front of the price so that it will sort properly. Re-read my post dated 18 Oct 06 12:43.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom