Talk:Transact-SQL

From Wikipedia, the free encyclopedia

Some believe that "In order to make it more powerful" is not quite the real intention of Transact-SQL to exist. The reason would be Vendor lock-in.

Contents

[edit] Text preserved from "Transact SQL"

From the page previously at Transact SQL (now a redirect), copied here in case it's useful:

Transact-SQL enhancements (or extensions) include control-of-flow language, stored procedures, triggers, defaults, rules, error handling and set options.

—Simon 20:03, 15 August 2005 (UTC)

[edit] "control of flow"

"Control-of-flow Language" is awkward, but that's what MSDN and Books Online use. [1] Let's keep it consistent with the official docs. -- Mikeblas 05:43, 7 January 2006 (UTC)

Should a bit about stored procedures and functions be put in there? I was thinking something along the lines of the following, though if anyone could check me on this not being specific to the MS branch of tsql.

Aside from making it possible to store queries, stored procedures and functions allow for generalizing code for better re-use and/or clarity. (There are some differences between the two, though their effects may seem similar. For instance, when calling a procedure, and value not specified is assumed to be the default if there is one, while in functions, the keyword default must be passed explicitly to use the set default. ) A function may be used any place a table could be in a query, though it must be aliased[? could be wrong]

Select *
From fn_Foo() bar

The Case statement is TSQL's version of a switch, and may be used anywhere an sql expression could be. There are two versions

SELECT
   CASE @myVariable
   WHEN 10 THEN  dob
   WHEN 20 THEN  name
   WHEN 30 THEN  purpose
   ELSE somethingElse
   END
From tblFoo

Where @myVariable could have been anything that can be compared to the items between the "when" and "then"s

and

Select dob 
From tblFoo
WHERE purpose =
  CASE
  WHEN (@myVariable = 10) THEN 'educate'
  WHEN (@myOtherVariable =23) THEN 'eliminate'
  WHEN (@myVariable = 20 and @myOtherVariable = 40) THEN 'eat'
  ELSE 'walk'
  END

Where each expression between a when and then must evaluate to true or false. The parentheses are not needed.

There's articles on functions and stored procs; I don't see why we can't provide mention of them here and links to the other articles. The most notable difference between functions and stored procs is that functions are evaluated per-row, while a stored proc is exeucted once. (I've recently clarified this in the user defined function article.) Your use of CASE isn't controlling flow. --Mikeblas 21:34, 21 June 2006 (UTC)

[edit] datepart

The example DATEPART code is kind of ugly. How about this:

IF DATENAME(WEEKDAY,CURRENT_TIMESTAMP) IN ('Saturday','Sunday')
    PRINT 'Weekend.'
ELSE
    PRINT 'Weekday.'

This is much more readable that the current version using

DATEPART(dw,DATE())

twice. And since

DATE()

is a non-deterministic function, there's an ambiguity as to whether the two invocations of it should return the same value or not.

I don't think your example works in all locales. -- Mikeblas 01:27, 29 June 2006 (UTC)

You're right, of course, the names 'Saturday' and 'Sunday' are locale specific. But what is the goal here, to illustrate the coding principle of control of flow language or write bullet proof t-sql to figure out if it's the weekend or not? The existing example is a pretty obfuscated way of illustrating that t-sql can do this

if @x>1 print 'Bigger' else print 'Smaller'

and the output text is not locale transparent either.

I believe a pretty robust and locale independent piece of T-SQL to alert the user to the status of the current date as a weekend date is:
if datename(weekday,current_timestamp) in (datename(weekday,'1900-01-06'),datename(weekday,'1900-01-07')) 
    print case serverproperty('LCID')
               when 1033 then 'It is the weekend.'
               when 1031 then 'Es ist das Wochenende.'
               -- add French, Spanish, etc. to match userbase
               else raiserror('Unknown locale for message.',11,1)
          end
else
    print case serverproperty('LCID')
               when 1033 then 'It is a weekday.'
               when 1031 then 'Es ist ein Wochentag.'
               else raiserror('Unknown locale for message.',11,1)
          end

This only calls the non-deterministic function current_timestamp once so there is no ambiguity about the return value and specifies the weekend dates in an unambiguous and pre-compilable format. Output is modified according to server locale and comparisons are not collation specific. (It's also a joke.) --Farmhouse121 04:06, 30 June 2006 (UTC)

OTOH, this is is an encyclopedia, and it should reflect a global view. Its articles are translated to different languages and read by people all around the globe; if such a user reads a code sample, it should work where they live. If that means we need to pick a better example, then I'm all for it. -- Mikeblas 11:50, 30 June 2006 (UTC)

Yes, but this is the English version, so I think it's actually reasonable to localize the examples to common English usage. I go back to my comment that the point is to illustrate how the control of flow language works and not to produce bulletproof production code for some third party to implement. (The reason being that bulletproof code is not often a clean exposition of the concept.) I feel that the "datepart(dw,date())" etc. sequence is using lots of T-Sql knowledge that the naif would not know. The real question is who's reading this article and why? Clearly, one set of readers are T-Sql experts checking out what Wikipedia has on T-Sql (i.e. that's us). I would assume another, and significant, set of readers are experienced programmers who are looking to this as a resource to obtain a summary of the capabilities of T-Sql to aid in deciding whether the product is suitable for them. They're going to recognize what a "if then" statement does even if one just states "T-Sql possesses an IF <LOGICAL-EXPRESSION> <STATEMENT> ELSE <ALTERNATE-STATEMENT>". What do you think? --Farmhouse121 21:41, 3 July 2006 (UTC)

Maybe the best alternative is to get rid of datepart(/*args*/) and its locality, and pick a better example. -- Mikeblas 19:33, 5 July 2006 (UTC)

[edit] Article title

Shouldn't the title of this article be T-SQL like the title of SQL is SQL but not Structured Query Language? --Stefán Örvarr Sigmundsson (talk) 16:08, 16 April 2008 (UTC)