SQL cast, convert, QUOTENAME, exec function learning records

Syntax
Use CAST:

CAST (expression AS data_type)

Use CONVERT:

CONVERT (data_type [(length)], expression [, style])

Parameters
Expression

Is any valid Microsoft SQL Server "expression. For more information, see expressions.

Data_type

The data types provided by the target system, including bigint and SQL _variant. User-Defined data types cannot be used. For more information about available data types, see data types.

Length

Optional parameters of the nchar, nvarchar, char, varchar, binary, or varbinary data type.

Style

Date Format style, which converts datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type); or string format style, to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type ).

Explicitly converts a data type expression to another data type. For more information about available data types, see data types. Date Format style, which converts datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type); or string format style, to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type ). Output when converted to character data.
 

Implicit conversions are invisible to users.
SQL Server automatically converts data from one data type to another. For example, if a smallint variable is compared with an int variable, the smallint variable is implicitly converted to an int variable before the comparison.

Explicit conversions use the CAST or CONVERT functions.
The CAST and CONVERT functions CONVERT numeric values from one data type (local variable, column, or other expression) to another data type.

For example, the following CAST function converts the value $157.27 to the string ''' $157.27 '':CAST ($157.27 as varchar (10 ))
CAST functions are based on SQL-92 standards and take precedence over CONVERT.

Some implicit and explicit data types cannot be converted from the Data Type of one SQL Server object to another. For example, nchar values cannot be converted to image values. Nchar can only be converted to binary explicitly. implicit conversion to binary is not supported. Nchar can be converted to nvarchar explicitly or implicitly.

When processing SQL _variant data types, SQL Server supports implicit conversion of objects with other data types to SQL _variant type. However, SQL Server does not support implicit conversion from SQL _variant data to objects of other data types.

 Select convert (CHAR (10), CURRENT_TIMESTAMP, 102)

(102 indicates that the ANSI date mode is used, that is, the yy. mm. dd type)

However, if you want to explicitly generate this variable as a datetime or smalldatetime variable to be compatible in a specific database column, you can use the following statement:

Select cast (CONVERT (CHAR (10), CURRENT_TIMESTAMP, 102) AS DATETIME

The returned value will be yy. mm. dd 00:00:00 (for example, AM as the timestamp;

Function QUOTENAME
--Function: returns a Unicode string with a separator. Adding a separator can make the input string a valid Microsoft SQL Server 2005 separator.
--Syntax
QUOTENAME('Character_string' [, 'Quote _ character'])

--Example:

--For example, you have a table named index.
--You have a dynamic query. The parameter is the table name.
Declare @ Tbname Varchar(256)
Set @ Tbname='Index'
---Query the data in this table:
Print('Select * from'+@ Tbname)
Exec('Select * from'+@ Tbname)

--In this way, the print data is
Select * From Index

--Because index is a key word, it must have an error. You can add brackets:
Select * From [Index]

--QUOTENAME is available, namely:
Print('Select * from'+QUOTENAME(@ Tbname))
--Result: select * from [index]
Exec('Select * from'+QUOTENAME(@ Tbname))

The EXEC command can be used to execute a stored procedure or a dynamic batch. A batch is a string containing SQL statements.
For example:
Declare @ schemaName varchar (80), @ tableName varchar (80 ),
@ ObjName varchar (512 );
Set @ schemaName = 'dbo ';
Set @ tableName = 'Orders ';
Set @ objName = @ schemaName + '.' + @ tableName;
Exec ('select count (*) from '+ @ objName );

Note that only string variables and string constants are allowed in exec brackets. You cannot call a function or use a case expression here.
The following code is incorrect:
Exec ('select count (*) from '+ quotename (@ objName ));

So,The basic method is to save the statement to a variable, for example:
Set @ SQL = '...';
Exec (@ SQL );
This does not have the above restrictions.

1 EXEC no interface
The unique input of Exec is a string. A dynamic batch does not have access to the local variables defined in the batch that calls it:
Declare @ I int
Set @ I = 1;
Declare @ SQL varchar (255)
Set @ SQL = 'select * from dbo. orders where rderid = @ I ';
Exec (@ SQL );
Error: must declare scalar variable @ I
The reason is that @ I cannot be placed within ''. It can be dynamically embedded into SQL statements through string connection:
Set @ SQL = 'select * from dbo. orders where rderid = '+ cast (@ I as varchar (10 ));

The connection between variables and strings can lead to a so-called SQL Injection security risk if the variables contain strings. One way to prevent SQL injection is to limit
The size of the string.
This connection will cause an image of performance. The SS creates an execution plan for each string, regardless of whether the two strings are in the same mode. To this end,
An example is shown below:
First, clear the execution plan in the cache:
DBCC freeproccache;
Then, run the following code three times and set @ I to 10248, 10249, and 10250 respectively.
DECLARE @ I AS INT;
SET @ I = 10248;

DECLARE @ SQL AS VARCHAR (52 );
SET @ SQL = 'select * FROM dbo. Orders WHERE rderID ='
+ CAST (@ I AS VARCHAR (10) + N ';';
EXEC (@ SQL)
Finally, query sys. syscacheobjects:
SELECT cacheobjtype, objtype, usecounts, SQL
FROM sys. syscacheobjects
WHERE SQL NOT LIKE '% cache %'
AND SQL NOT LIKE '% sys. % ';
The result is displayed as follows:
It generates an execution plan for each query. And a parameterized execution plan.

EXEC has no output parameters. By default, exec returns the output of this query to the caller. If you want to save the result to a variable, you must
Use the Insert EXEC syntax, read the data from the table, and store the data in the target variable.
DECLARE
@ Schemaname as nvarchar (128 ),
@ Tablename as nvarchar (128 ),
@ Colname as nvarchar (128 ),
@ SQL AS NVARCHAR (805 ),
@ Cnt as int;

SET @ schemaname = N 'dbo ';
SET @ tablename = n' Orders ';
SET @ colname = n' mermerid ';
SET @ SQL = n' SELECT COUNT (DISTINCT'
+ QUOTENAME (@ colname) + N') from'
+ QUOTENAME (@ schemaname)
+ N '.'
+ QUOTENAME (@ tablename)
+ N ';';

Create table # T (cnt INT );
Insert into # T
EXEC (@ SQL );
SET @ cnt = (SELECT cnt FROM # T );
SELECT @ cnt;
Drop table # T;

Note: If you forget to enter the last statement "Drop ...", The following annoying error will occur:
The database already has an object named '# t.

In the above section, a temporary table is created, which is visible to dynamic batches. Therefore, you can modify the above program:
SET @ SQL = n' INSERT INTO # T (cnt) SELECT COUNT (DISTINCT'
+ QUOTENAME (@ colname) + N') from'
+ QUOTENAME (@ schemaname)
+ N '.'
+ QUOTENAME (@ tablename)
+ N ';';

Create table # T (cnt INT );
EXEC (@ SQL );
SET @ cnt = (SELECT cnt FROM # T );
SELECT @ cnt;
Drop table # T;
Because EXEC is executed after Create Table, the Insert statement can be moved to the definition of @ SQL.

2 variable connection
In SS2000, one of the advantages of EXEC over sp_executesql is that it supports a longer length of input code. Although, technically speaking, the latter's
The input code string is of the NTEXT type and usually needs to be saved with a local variable. However, local variables cannot be declared as large objects.
Therefore, the maximum length supported by sp_executesql is the Unicode String Length (NVARCHAR, 4000 characters ). While
EXEC, supports regular strings (VARCHAR), that is, 8000 characters.
In addition, EXEC supports the connection of multiple variables. Each variable has a maximum of 8000 characters.

However, in SS2005, the variable type can be VARCHAR (max), and the maximum value is 2 GB.

3 EXEC
This is the new syntax in 05. Execute dynamic SQL statements on the remote host

Plus N indicates that the database is stored in Unicode format.
N 'string' indicates that string is a Unicode string.

The format of a Unicode string is similar to a regular string, but it is preceded by an N identifier (N stands for the international Language in the SQL-92 standard )). The N prefix must be an uppercase letter. For example, 'michél' is a String constant, while n'michél' is a Unicode constant. Unicode constants are interpreted as Unicode data and are not calculated using the code page. Unicode constants do have sorting rules, mainly used to control comparison and Case sensitivity. Assign the default sorting rules of the current database to Unicode constants unless the sorting rules are specified for them using the COLLATE clause. Each character in Unicode data is stored in two bytes, while each character in character data is stored in one byte. For more information, see use Unicode data.

Unicode string constants support enhanced sorting rules

The database name is an identifier, and the table name is also an identifier. in SQL SERVER, there are two types of identifiers:

There are two types of identifiers:

Regular identifier

Complies with the format rules of identifiers. Do not separate regular identifiers in a Transact-SQL statement.

SELECT *FROM TableXWHERE KeyCol = 124

Separator

Enclosed in double quotation marks (") or square brackets. Identifiers that comply with the identifier format rules can be separated or not separated.

SELECT *FROM [TableX]     --Delimiter is optional.WHERE [KeyCol] = 124 --Delimiter is optional.

In a Transact-SQL statement, identifiers that do not comply with the rules of all identifiers must be separated.

SELECT *FROM [My Table]   --Identifier contains a space and uses a reserved keyword.WHERE [order] = 10  --Identifier is a reserved keyword.

The regular and delimiter identifiers must contain 1 to 128 characters. For a local temporary table, the identifier can contain a maximum of 116 characters.

Important differences between the two:Regular identifiers must strictly abide by naming rules, while separated identifiers can be separated by [] and.

Identifier format:
　　
1. The identifier must be a character specified in the Unicode 2.0 standard and a number of other language characters. Such as Chinese characters.
　　
2. The characters after the identifier can be (except for condition 1) "_", "@", "#", "$", and numbers.
　　
3. The identifier cannot be a reserved word of Transact-SQL.
　　
4. Empty and special characters are not allowed in the identifier.
　　
In addition, some identifiers starting with special characters have specific meanings in SQL SERVER.

For exampleThe identifier starting with "@" indicates a local variable or a function parameter. The identifier starting with "#" indicates a temporary table or a stored procedure.

Starting with "#" indicates that this is a global temporary database object. T

The global variables of ransact-SQL start.

An identifier can contain up to 128 characters.