March 10, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/howdynamiccanstaticbe.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 12, 2002 at 3:59 pm
I am trying to use this article's advice, but am getting wierd results. When I sort by any field which is date or numeric (proj_id or request_received) it works fine - when I try to sort by any char field (such as proj_name) I get the following error...
Server: Msg 241, Level 16, State 1, Line 7
Syntax error converting datetime from character string.
I don't understand this at all - I can remove the case statement and substitute proj_name in the order by clause and it works fine.
declare @proj_id_inint,
@order_by_inchar(30)
set @proj_id_in = NULL
set @order_by_in = 'proj_lead'
SELECT a.proj_id, a.proj_name, a.proj_desc, a.proj_priority, convert(char(10),a.request_received,101) as 'request_received', convert(char(10),a.due_date,101) as 'due_date',
convert(char(10),a.date_completed,101) as 'date_completed',
b.team_desc, c.emp_lname + ', ' + c.emp_fname as 'proj_lead', a.client_contact, d.status_desc
from cts_proj a
JOIN cts_team b on a.team_code = b.team_code
JOIN cts_emp c on a.proj_lead = c.emp_id
JOIN cts_status d on a.status_code = d.status_code
WHERE (@proj_id_in IS NULL OR a.proj_id = @proj_id_in)
order by
CASE rtrim(@order_by_in)
WHEN 'proj_id' THEN a.proj_id
WHEN 'proj_name' THEN a.proj_name
WHEN 'proj_priority' THEN a.proj_priority
WHEN 'requested_date' THEN a.request_received
WHEN 'due_date' THEN a.due_date
WHEN 'completion_date' THEN a.date_completed
WHEN 'proj_lead' THEN a.proj_name
WHEN 'client_contact' THEN client_contact
WHEN 'status_desc' THEN status_desc
END
March 12, 2002 at 4:10 pm
If you look at example 2 of my article you will notice that the two times I use the CASE function with a datetime field I cast the datetime field as varchar. All datetime fields must be cast as varchar (or char) when you mix datatypes in your CASE function.
So for the code you posted in the ORDER BY you should replace a.date_completed with CAST(a.date_completed as varchar(20)).
This will make your code not fail with the error you are getting, however when sorting by the datetime column converted to varchar you still won't get the sorting exactly correct so you'll probably need to manipulate the datetime column to put the year first, then the month, and finally the day so that it will sort correctly. Let me test this with example 2 and then I'll post the sample code here.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 12, 2002 at 4:23 pm
This is how example 2 can be reworked with a CASE function in the ORDER BY clause that properly sorts by the datetime field:
DECLARE @column varchar(10)
SET @Column = 'title'
SELECT EmployeeID,
CASE @column
WHEN 'Name' THEN LastName
WHEN 'Title' THEN Title
ELSE LastName
END AS Column1,
CASE @column
WHEN 'Name' THEN FirstName
WHEN 'Title' THEN LastName
ELSE CAST(BirthDate as varchar(20))
END AS Column2,
CASE @column
WHEN 'Title' THEN CAST(HireDate as varchar(20))
ELSE ''
END AS Column3, HireDate
FROM employees
ORDER BY CASE @column WHEN 'Title' THEN
LTRIM(STR(DATEPART(yyyy,HireDate)))
+ CASE LEN(LTRIM(STR(DATEPART(mm,HireDate)))) WHEN 1 THEN '0' + LTRIM(STR(DATEPART(mm,HireDate)))
ELSE LTRIM(STR(DATEPART(mm,HireDate))) END
+ CASE LEN(LTRIM(STR(DATEPART(dd,HireDate)))) WHEN 1 THEN '0' + LTRIM(STR(DATEPART(dd,HireDate)))
ELSE LTRIM(STR(DATEPART(dd,HireDate))) END
WHEN 'Name' THEN FirstName END
The could you would need to adapt to your datetime columns is this:
LTRIM(STR(DATEPART(yyyy,HireDate)))
+ CASE LEN(LTRIM(STR(DATEPART(mm,HireDate)))) WHEN 1 THEN '0' + LTRIM(STR(DATEPART(mm,HireDate)))
ELSE LTRIM(STR(DATEPART(mm,HireDate))) END
+ CASE LEN(LTRIM(STR(DATEPART(dd,HireDate)))) WHEN 1 THEN '0' + LTRIM(STR(DATEPART(dd,HireDate)))
ELSE LTRIM(STR(DATEPART(dd,HireDate))) END
This would come after the THEN keyword, simply change the column name to the one you plan to use.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 12, 2002 at 4:32 pm
Sorry - I missed the part in sample 2 on CASTing dates. I changed my code to convert my dates and then found that I needed to convert eveything that was not CHAR to CHAR including my dates and integers. So my ORDER BY clause ended up like this and it appears to work great:
ORDER BY
CASE rtrim(@order_by_in)
WHEN 'proj_id' THEN convert(char(10),a.proj_id)
WHEN 'proj_name' THEN a.proj_name
WHEN 'proj_priority' THEN convert(char(10),a.proj_priority)
WHEN 'requested_date' THEN convert(char(10),a.request_received,101)
WHEN 'due_date' THEN convert(char(10),a.due_date,101)
WHEN 'completion_date' THEN convert(char(10),a.date_completed,101)
WHEN 'proj_lead' THEN c.emp_lname + c.emp_fname
WHEN 'client_contact' THEN a.client_contact
WHEN 'status_desc' THEN d.status_desc
March 12, 2002 at 4:38 pm
Yes, what you have now won't give you an error.
Make sure when you sort by the date column that it is sorting it in the order you want. I think you'll find that it sorts incorrectly without the additional code I gave in my last post.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 12, 2002 at 4:54 pm
You were correct - it did sort incorrectly. I made my dates YYYYMMDD using this code:
WHEN 'due_date' THEN LTRIM(STR(DATEPART(yyyy,a.due_date))) +
RIGHT('00'+LTRIM(STR(DATEPART(mm,a.due_date))),2) +
RIGHT('00'+LTRIM(STR(DATEPART(dd,a.due_date))),2)
Everything looks like it is working fine now including date sorting. Excellent way to contruct some dynamic clauses without dynamic SQL!
March 12, 2002 at 4:55 pm
Yes it is extremely useful.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 14, 2002 at 7:55 am
Actually, I think a convert(char(8),myDate,112) would be easier to get the date into an ISO format than all of those dateparts and casts.
March 14, 2002 at 12:40 pm
I agree with tmorton, your objective in SQL queries and functions is to call the least number possible, this genrally has a total effect on the server in CPU utilization and memory access. Even though this may be small consider if you have 100+ users all running queires with that type code and running them multiple times, the cost savings is cumulative.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 14, 2002 at 1:06 pm
Yes the way tmorton suggested is certainly better. I got too caught up with using DATEPART and failed to find a simpler way.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 12, 2012 at 5:48 pm
Hi,
I have a question regarding dynamic SQL / Static SQL.
I have written queries along with code in VB.NET & not in SP.
Example : SELECT * FROM TABLE WHERE COLUMN1 = ? AND COLUMN2 = ?
? are variables either string or int.
Above Query is Dynamic SQL or Static SQL?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply