Talk:Transact-SQL

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.

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)[reply]

"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)[reply]

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)[reply]

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)[reply]

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 and the output text is not local transparent either. To do that you'd have to use raiserror to generate a locale specific message, right? It's just a thought...

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