Sqlcast, convert, QUOTENAME, exec function learning records

Explicitly converts a data type expression to another data type. CAST and CONVERT provide similar functions.

Explicitly converts a data type expression to another data type. CAST and CONVERT provide similar functions.

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

(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:



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

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 ));

Therefore, there is no such restriction.

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

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

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:

　　
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 example