How Dynamic SQL Can Be Static SQL

  • Robert W Marda

    SSChampion

    Points: 13413

    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

  • psstarkey

    SSC Veteran

    Points: 281

    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

    SSChampion

    Points: 13413

    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

  • Robert W Marda

    SSChampion

    Points: 13413

    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

  • psstarkey

    SSC Veteran

    Points: 281

    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

    SSChampion

    Points: 13413

    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

  • psstarkey

    SSC Veteran

    Points: 281

    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

    SSChampion

    Points: 13413

    Yes it is extremely useful.

    Robert Marda

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • tmorton

    SSC Journeyman

    Points: 77

    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

    SSC Guru

    Points: 125444

    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)

  • Robert W Marda

    SSChampion

    Points: 13413

    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

  • Brian4

    Grasshopper

    Points: 17

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply