Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How Dynamic SQL Can Be Static SQL Expand / Collapse
Author
Message
Posted Sunday, March 10, 2002 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/howdynamiccanstaticbe.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #2960
Posted Tuesday, March 12, 2002 3:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 27, 2012 6:05 AM
Points: 23, Visits: 10
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_in	int,
@order_by_in char(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





Post #29599
Posted Tuesday, March 12, 2002 4:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #29600
Posted Tuesday, March 12, 2002 4:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #29601
Posted Tuesday, March 12, 2002 4:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 27, 2012 6:05 AM
Points: 23, Visits: 10

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





Post #29602
Posted Tuesday, March 12, 2002 4:38 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #29603
Posted Tuesday, March 12, 2002 4:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 27, 2012 6:05 AM
Points: 23, Visits: 10
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!




Post #29604
Posted Tuesday, March 12, 2002 4:55 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Yes it is extremely useful.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #29605
Posted Thursday, March 14, 2002 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 16, 2006 8:18 AM
Points: 1, Visits: 1
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.




Post #29606
Posted Thursday, March 14, 2002 12:40 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, August 18, 2014 12:08 PM
Points: 8,369, Visits: 735
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)



Post #29607
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse