Yet another CASE statement question

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)

Yet another CASE statement question

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2001-12-03 : 17:57:15

I am building an ASP application and would like the user to be able to sort by any column in the table. There are 8 columns, seven of which are varchars and one is a datetime. Here is part of the query:

INSERT INTO #Temp(Entry_ID)
SELECT Entry_ID
FROM Log_Entries
WHERE Event_ID = 5 AND
EventTime >= @eventtime AND
Computer = @computer
ORDER BY CASE @ordercol
WHEN 'username' THEN UserName
WHEN 'virus' THEN Virus
WHEN 'viruslocation' THEN VirusLocation
WHEN 'actiontaken' THEN ActionDescription
WHEN 'virusdescription' THEN VirusDescription
WHEN 'definfo' THEN DefInfo
WHEN 'parent' THEN Parent
ELSE EventTime
END
------------------------------------------
@eventtime is an input parameter to the proc and has the value: '11/01/2001' and the datatype: datetime

As long as EventTime is part of the CASE statement I get the following error:
Syntax error converting datetime from character string.

'11/01/2001' should be a valid datetime. Why would there be an error converting datetime from character string? Actually, why would it ever be a character string? Do all columns in the ORDER BY CASE statement have to be of the same datatype? That would not seem very useful.

Thanks,
Chris

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-03 : 18:07:36

Yes, they do need to be of the same datatype. You can fix it pretty easily:

INSERT INTO #Temp(Entry_ID)
SELECT Entry_ID
FROM Log_Entries
WHERE Event_ID = 5 AND
EventTime >= @eventtime AND
Computer = @computer
ORDER BY CASE @ordercol
WHEN 'username' THEN UserName
WHEN 'virus' THEN Virus
WHEN 'viruslocation' THEN VirusLocation
WHEN 'actiontaken' THEN ActionDescription
WHEN 'virusdescription' THEN VirusDescription
WHEN 'definfo' THEN DefInfo
WHEN 'parent' THEN Parent
ELSE Convert(varchar(30), EventTime, 121)
END

Why are you even bothering with ORDER BY when you're performing an INSERT? It will not guarantee that the rows will be stored in that order. Considering the EventID is the only column being INSERTed, this makes no sense.

cbrinson
Starting Member

36 Posts

Posted - 2001-12-03 : 18:54:11

quote:

Why are you even bothering with ORDER BY when you're performing an INSERT? It will not guarantee that the rows will be stored in that order. Considering the EventID is the only column being INSERTed, this makes no sense.

That section is only part of the proc. I am selecting the primary key column (an identity field) from the source table (log_entries) and inserting it into a temp table with its own identity field (#temp). I don't see how the rows would not be stored the way that I insert them. I can then select only the records that I need and return them to the ASP page. (The table has hundreds of thousands of records.) For example, if the user is viewing page 10 and there are 50 records per page then I only need to select rows 451-500 from the temp table and join that to the log_entries table on temp.entry_id = log_entries.entry_id

I think this is how most bulletin boards like this one work? You would not want to return 500,000 records to ASP. If there is a better way to do this though I would definitely be interested.

Thanks,
Chris

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-03 : 19:09:21

Chris,

What you are doing, will indeed cause your temp tables Identity field to be allocated/incremented in the order you are expecting. Whether the data is really STORED in that order (and whether this even matters on a temp table vs. a real table) is another debate. I think Rob's comment comes from the fact that you can't guarantee the physical order data is stored in a table, so other than controlling the order that Identity is assigned, an ORDER BY on an Insert would be meaningless.

For your situation, it looks like it will be fine.

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources