A big hello and 2 questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A big hello and 2 questions

Author

Topic

davearia
Starting Member

3 Posts

Posted - 2006-10-01 : 19:14:21

First of all hello to everyone at this forum. I have worked as a .NET developer for about 3 years now in which time I have worked with SQL Server.

I am trying to write a stored procedure that is dynamic, trying to avoid having many select statements surrounded by if clauses etc.
Here is a rough draft of SQL of what I am working on:


DECLARE	
	@StartDate  		DATETIME,
	@EndDate  		DATETIME,
	@UseStartDateCriteria 	BIT,
	@UseEndDateCriteria 	BIT,
	@ErrorTypeCriteria 	VARCHAR(50),
	@ErrorID  		BIT,
	@ErrorType 		BIT,
	@Server  		BIT,
	@OrderBy  		VARCHAR(50),
	@AscDesc 		VARCHAR(50)
SET DATEFORMAT DMY
SET @StartDate = '18/09/2006'
SET @EndDate = '18/10/2006'
SET @EndDate = DATEADD(HOUR, 23, @EndDate)
SET @EndDate = DATEADD(MINUTE, 59, @EndDate)
SET @EndDate = DATEADD(MINUTE, 59, @EndDate)
SET @ErrorTypeCriteria = 'Errors'
SET @ErrorID = 1
SET @ErrorType = 0
SET @Server = 1
SET @OrderBy = 'ErrorDate'
SET @AscDesc = 'ASC'

SELECT   
	CASE WHEN   @ErrorID = 1 THEN ErrorID END, 
	CASE WHEN   @ErrorType = 1 THEN  ErrorType END, 
	CASE WHEN   @Server = 1 THEN  Server END, 
	FROM         
	[dbo].[ErrorDetails]
WHERE 
	ErrorType = @ErrorTypeCriteria
	AND ErrorDate >= COALESCE(@StartDate,ErrorDate)
	AND ErrorDate <= COALESCE(@EndDate,ErrorDate)
ORDER BY
CASE 
	WHEN @OrderBy = 'ErrorID' THEN ErrorID 
	WHEN @OrderBy = 'ErrorType' THEN ErrorType
	WHEN @OrderBy = 'Server' THEN Server
	ELSE ErrorDate
END

The first thing I am trying to do is depending on is only select a column if its corresponding bit value is passed in as 1. Let me give an example. In the code above I pass in a parameter ErrorID as a value of 1. In this case I wish the select statement to include ErrorID in the select statement. In the case of ErrorType passed in as 0 I wish this not to be part of the select statement. Iam trying to make the call to the database less draining. Hope this makes sense. As you can see I have made an unsuccessful attempt of this already.

Secondly, I am trying to order by dynamically, partly I have had some luck with this. I have managed to dynamically set the field to order by but in addition to this I wish to set the ascending/descending depending on what parameter @AscDesc passes in. I don't seem to have the right code up to now after trying many things.

If you can help, please do, thanks, Dave.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-10-01 : 19:23:40

the TSQL CASE statement doesn't work this way. If you want to change what you are selecting, then you will either have to set up a series of IF statements with corresponding SELECT statements or build dynamic SQL.

Dynamic SQL is potentially going to be slower, it will require that your sproc get compiled each time it is run and it will possibly be more difficult to read.

-ec

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-01 : 20:07:57

Having different resultsets returned from the proc depending on the parameters is a bad idea.
See the proc as a contract, interface if you want, between the client and the database.
The result of a proc should always return the same columns.

Write one proc for each situation, or let the client only use the columns needed.

The order by, as you already found out can be dynamic.

rockmoose

davearia
Starting Member

3 Posts

Posted - 2006-10-02 : 04:21:29

So the second point I was asking about, ordering by dynamically. How do I order ascending/descending dyamically as well as order by a field dynamically which I have already done?

Cheers, Dave.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-02 : 09:50:54

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

davearia
Starting Member

3 Posts

Posted - 2006-10-02 : 10:12:45

That is a really useful link thanks a lot I'll let you know how I get on.

Thanks, Dave.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-02 : 11:43:27

You can order like this for example.
0 = no ordering
1 = ASC
-1 = DESC

declare @orderbyID int
declare @orderByName int
declare @orderByType int

set @orderByID = 0	-- don't order by id
set @orderByName = -1	-- order by Name DESC
set @orderByType = 1	-- order by Type ASC


select	id
	,name
	,type
from	dbo.sysobjects
order by
	case	when @orderByType = 1	then type	end ASC
	,case	when @orderByType = -1	then type	end DESC
	,case	when @orderByName = 1	then name	end ASC
	,case	when @orderByName = -1	then name	end DESC
	,case	when @orderByID = 1	then id		end ASC
	,case	when @orderByID = -1	then id		end DESC

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources