Talk:Transact-SQL

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)

"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.

the above CASE examples are not flow control, and furthermore are standard SQL99. it's a good thing they are not on the main pageJasen betts (talk) 08:28, 12 February 2011 (UTC)

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)

Reason?

”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. Oh, bullshit. —Preceding unsigned comment added by 18.26.4.40 (talk) 18:04, 14 October 2008 (UTC)

No "while" example given

Under section "Flow control", it says at the end "An example of a WHILE loop is given below.", but no such example is given. Is it safe to delete that or is someone working on an example? 138.80.0.10 (talk) 15:36, 9 April 2010 (UTC)

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)

The current code assumes (without saying so) that Sunday is configured as the first day of the week. This is a per-server setting and can vary. 82.113.133.21 (talk) 09:38, 11 April 2012 (UTC)

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)

no from clause?

How can you execute a delete statement with no from clause? Is that for deleting the entire database or something? Seems like there's an obvious answer, but I can't think of it. --207.235.73.130 (talk) 14:16, 23 September 2011 (UTC)

+1, Changes to DELETE and UPDATE statements section need an explanation. I've never seen a DELETE without a FROM. And MySQL have the possibility to delete on multiple tables since 4.0 (2001-2003). Lacrymocéphale 16:26, 30 September 2012 (UTC)

I don't see anything there about a DELETE without a FROM clause, either now or in the version as of your comment. But I'm puzzled by "both the DELETE and UPDATE statements allow a FROM clause to be added". Since DELETE needs a FROM clause anyway, this can only mean a DELETE statement can accept two FROM clauses. But the DELETE example there has only one FROM clause, so what's it on about? — Smjg (talk) 21:39, 26 July 2019 (UTC)

Dates?

Some dates would be nice, when was it released, versions etc.. --86.14.120.74 (talk) 17:15, 21 April 2012 (UTC)

I agree - at the moment, there's no history information at all. There ought to be, especially as it has a long history. I get the impression that T-SQL was created at a time when standard SQL statements were executed only one at a time, thus there was no pre-existing concept of an SQL script. Thus there was a subsequent divergence between T-SQL, which just juxtaposed statements to be executed in sequence with no delimiter, and standard SQL which introduced the semicolon as a statement terminator to support such scripting. Nowadays the two are slowly coming back together. It would be good if we could find some sourced info to put into the article about this. Maybe I'll try and find some when I've a bit more time. — Smjg (talk) 11:24, 18 November 2022 (UTC)