SQLServerCentral / Discuss Content Posted by Robert Marda / Article Discussions / Article Discussions by Author / How Dynamic SQL Can Be Static SQL / Latest Posts

RE: How Dynamic SQL Can Be Static SQL

Brian4 — Thu, 12 Jul 2012 17:48:53 GMT

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?

RE: How Dynamic SQL Can Be Static SQL

Robert W Marda — Thu, 14 Mar 2002 13:06:00 GMT

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

RE: How Dynamic SQL Can Be Static SQL

Antares686 — Thu, 14 Mar 2002 12:40:00 GMT

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)

RE: How Dynamic SQL Can Be Static SQL

tmorton — Thu, 14 Mar 2002 07:55:00 GMT

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.

RE: How Dynamic SQL Can Be Static SQL

Robert W Marda — Tue, 12 Mar 2002 16:55:00 GMT

Yes it is extremely useful.Robert Marda

RE: How Dynamic SQL Can Be Static SQL

psstarkey — Tue, 12 Mar 2002 16:54:00 GMT

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!

RE: How Dynamic SQL Can Be Static SQL

Robert W Marda — Tue, 12 Mar 2002 16:38:00 GMT

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

RE: How Dynamic SQL Can Be Static SQL

psstarkey — Tue, 12 Mar 2002 16:32:00 GMT

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

RE: How Dynamic SQL Can Be Static SQL

Robert W Marda — Tue, 12 Mar 2002 16:23:00 GMT

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, HireDateFROM employeesORDER 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 ENDThe 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))) ENDThis would come after the THEN keyword, simply change the column name to the one you plan to use.Robert Marda

RE: How Dynamic SQL Can Be Static SQL

Robert W Marda — Tue, 12 Mar 2002 16:10:00 GMT

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

RE: How Dynamic SQL Can Be Static SQL

psstarkey — Tue, 12 Mar 2002 15:59:00 GMT

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 7Syntax 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 = NULLset @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_descfrom 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_codeWHERE (@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

How Dynamic SQL Can Be Static SQL

Robert W Marda — Sun, 10 Mar 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/howdynamiccanstaticbe.asp