830 likes | 948 Views
Batches and the GO command. GO. To signal the end of a batch, you use the GO command. A GO command isn’t required after the last batch in a script or for a script that contains a single batch. Concatenation, cast, convert. Concatenation ( + ). The SQL standard uses || for concatentation
E N D
Batches and the GOcommand © 2002, Mike Murach & Associates, Inc.
GO • To signal the end of a batch, you use the GO command. • A GO command isn’t required after the last batch in a script or for a script that contains a single batch. © 2002, Mike Murach & Associates, Inc.
Concatenation, cast, convert © 2002, Mike Murach & Associates, Inc.
Concatenation ( + ) • The SQL standard uses || for concatentation • Sql Server TSql uses + for concatenation • Example:SELECT fname + ' ' + lname as name FROM employees © 2002, Mike Murach & Associates, Inc.
operands must be character data • In SQL Server - TSql ...concatenation MUST take two character values (varchar, char, nchar, etc) as its operands • Neither parameter can be numeric or date types • The following WILL cause an ERROR (if salary is a money column) SELECT fname + salary FROM employees © 2002, Mike Murach & Associates, Inc.
cast • Use the SQL standard "cast" operator to convert data from one datatype into another • The following WILL work:SELECT fname + cast(salary as varchar(10))FROM employees © 2002, Mike Murach & Associates, Inc.
convert • You can use the TSQL "convert" function instead of cast. • The following is equivalent to the previous example:SELECT fname + convert(varchar(10), salary)FROM employees © 2002, Mike Murach & Associates, Inc.
Difference between CAST and CONVERT • differences • standards • CAST is a standard • CONVERT is TSQL specific • extra functionality • CONVERT has one additional piece of functionality that CAST does not have • see next slide © 2002, Mike Murach & Associates, Inc.
Convert (type, data, style) • Convert takes an optional 3rd (integer) parameter that determines the "style" (AKA format) of the data after the cast. • If the data is a money value then format has the following possible values:0 - no commas, two decimal points1 - yes commas, two decimal points2 - no commas, four decimal points © 2002, Mike Murach & Associates, Inc.
styles for dates, float, real • See the following URL: • http://doc.ddart.net/mssql/sql70/ca-co_1.htm © 2002, Mike Murach & Associates, Inc.
TSQL statements © 2002, Mike Murach & Associates, Inc.
USE statement • USE <databaseName> • The use statement switches a script to use the specified database. • A single script can work in more than one database by issuing the use statement several times © 2002, Mike Murach & Associates, Inc.
PRINT statement • PRINT <string expression> • Prints out the value of the string expression. © 2002, Mike Murach & Associates, Inc.
Variables © 2002, Mike Murach & Associates, Inc.
Table Variables © 2002, Mike Murach & Associates, Inc.
Scalar vs. table variables A The variables that we've seen so far can hold a single data item and is defined with a standard data type. These are called scalar variables. A variable that can hold an entire table is called a "table variable" (see next slide ...) © 2002, Mike Murach & Associates, Inc.
Derived tables © 2002, Mike Murach & Associates, Inc.
Derived Tables • The term "Derived Table" is simply a table that is created as the result of a subquery in the FROM clause. © 2002, Mike Murach & Associates, Inc.
Temporary Tables © 2002, Mike Murach & Associates, Inc.
IF BEGIN ... ENDELSE BEGIN ... END © 2002, Mike Murach & Associates, Inc.