Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Dynamic SQL Can Be Static SQL


How Dynamic SQL Can Be Static SQL

Author
Message
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 82
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

psstarkey
psstarkey
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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




Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 82
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

Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 82
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

psstarkey
psstarkey
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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




Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 82
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

psstarkey
psstarkey
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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!



Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1110 Visits: 82
Yes it is extremely useful.

Robert Marda



Robert W. Marda
SQL Programmer
Ipreo

tmorton
tmorton
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: Moderators
Points: 8800 Visits: 780
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)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search