SQL cast,convert,quotename,exec function Learning record _mssql

Grammar
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. You cannot use user-defined data types. For more information about the available data types, see Data types.

Length

Optional parameters for the nchar, nvarchar, char, varchar, binary, or varbinary data types.

Style

Date format style, whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or string format styles, whereby float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Explicitly converts an expression of a data type to another data type. For more information about the available data types, see Data types. Date format style, whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or string format styles, whereby float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). Output when converted to character data.

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

An explicit conversion uses the cast or CONVERT function.
The cast and CONVERT functions convert a numeric value from one data type (local variable, column, or other expression) to another data type.

For example, the following cast function converts a numeric $157.27 to a string ' $157.27 ':cast ($157.27 as VARCHAR (10))
The CAST function is based on the SQL-92 standard and takes precedence over CONVERT.

Some implicit and explicit data-type conversions are not supported when converting from one SQL Server object data type to another. For example, a nchar value cannot be converted to an image value at all. NChar can only be converted explicitly to binary, and implicit conversion to binary is not supported. NChar can be explicitly or implicitly converted into nvarchar.

When working with sql_variant data types, SQL Server supports the implicit conversion of objects with other data types to sql_variant types. However, SQL Server does not support objects that are implicitly converted from sql_variant data to other data types.

SELECT CONVERT (CHAR, Current_timestamp, 102)

(102 indicates the use of ANSI date mode, that is, YY.MM.DD type)

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

SELECT CAST (CONVERT (CHAR, current_timestamp,102) as DATETIME

The return value will be yy.mm.dd 00:00:00 (such as 12:00am as a timestamp;

function QuoteName
--Function: Returns a delimited Unicode string with the addition of the delimiter to make the entered string a valid Microsoft SQL Server 2005 delimited identifier.
--Grammar
QuoteName('Character_string'[, ' Quote_character '])

--An example is provided:

--For example, you have a table named index
--You have a dynamic query, the parameter is the table name
Declare@tbnamevarchar(256)
Set@tbname='Index'
---Check the data in this table:
Print('SELECT * FROM'+@tbname)
Exec('SELECT * FROM'+@tbname)

-- This print data is
select   *   from   Index

-- because index is a word, there must be an error, plus parentheses:
Select   *   from   [ index ]

-- This has quotename, that is:
Print ( ' select * from  ' + quotename ( @tbname ))
-- results: Select * from [index]
exec ( ' select * from  ' + quotename ( @tbname ))

The EXEC command has two uses: executes a stored procedure, or executes a dynamic batch. A batch is a string of content that is an SQL statement.
For example:
DECLARE @schemaName varchar, @tableName varchar (80),
@objName varchar (512);
Set @schemaName = ' dbo ';
Set @tableName = ' Orders ';
Set @objName = @schemaName + '. ' + @tableName;
EXEC (' SELECT COUNT (*) from ' + @objName);

Note that in the exec parentheses, only string variables, string constants, are allowed. It is not allowed to call functions here or use case expressions.
The following code is wrong:
EXEC (' SELECT COUNT (*) from ' +quotename (@objName));

So the basic approach is to save the statement in a variable, such as:
Set @sql = ' ... ';
EXEC (@sql);
There is no such limit.

The only input to

    1 exec without interfaces
        exec is a string. A dynamic batch does not have access to the local variables defined within the batch that called it:
            declare @i int
            set @i=1;
            declare @sql varchar (255)
             Set @sql = ' SELECT * from Dbo.orders where rderid=@i ' ;
            exec (@sql);
        Error: Scalar variable @i must be declared
        The reason is that @i cannot be placed in ', it can only be dynamically embedded into the SQL statement via string concatenation:
            Set @sql = ' SELECT * from dbo.orders where rderid= ' +cast (@i as varchar);

A variable with a string connection raises the so-called SQL injection security risk If the variable contains a string. One way to hedge against SQL injection is to limit
The size of the string.
This connection can cause an image of performance. SS creates a separate execution plan for each string, regardless of whether the two string is a pattern. This
An example is shown below:
First clear the Cache execution plan:
DBCC Freeproccache;
Then execute the following code three times, set the @i to 10248,10249,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) + N '; ';
EXEC (@sql)
Last query sys.syscacheobjects:
SELECT Cacheobjtype, ObjType, usecounts, SQL
From sys.syscacheobjects
WHERE sql not like '%cache% '
and SQL not like '%sys.% ';
The results appear as follows:
It generates an execution plan for each query. And a parameterized execution plan.

EXEC also has no output parameters. By default, EXEC returns the output of the query to the caller. If you want to save the result in a variable, you must
Use the Insert EXEC syntax and read the data from the table and then store it 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 ' CustomerID ';
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 occurs: An object named ' #T already exists in the
        database.

    in the above program, a temporary table is created that is visible to dynamic batches. So you can modify the above program to:
        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 execution is after CREATE TABLE, the INSERT statement can be moved to the definition of @sql.

2-Variable Connection
In SS2000, exec is superior to sp_executesql in that it supports a longer code length. Although, technically speaking, the latter's
The input code string is a ntext type, and you usually want to save it with a local variable. However, local variables cannot be declared as large object types.
So, in fact sp_executesql's maximum supported string length is the length of the Unicode string (nvarchar,4000 characters). and
EXEC, the regular string (VARCHAR) is supported, that is, 8,000 characters.
Also, exec supports connections for multiple variables with a maximum of 8,000 characters per variable.

In SS2005, however, the variable type can be varchar (max), and the maximum is 2G.

3 EXEC at
This is the new grammar in 05. Executing dynamic SQL statements on a remote host

Plus N is stored in Unicode format when saved to the database.
N ' String ' indicates that string is a Unicode string

The Unicode string is formatted like an ordinary string, but it has a n identifier (n) that represents the international language (national Language) in the SQL-92 standard. The N prefix must be uppercase. For example, ' Michél ' is a string constant and N ' Michél ' is a Unicode constant. Unicode constants are interpreted as Unicode data and are not computed using code pages. Unicode constants do have collations that are primarily used to control comparisons and case sensitivity. Assign the default collation of the current database to a Unicode constant unless you specify a collation for it by using the COLLATE clause. Each character in Unicode data is stored using two bytes, while each character in the character data is stored using one byte. For more information, see Working with Unicode data.

Unicode string constants support enhanced sorting rules

The database name is an identifier, and the table name is also an identifier, and in SQL Server the identifiers fall into two categories:

There are two types of identifiers:

General identifiers

Format rules that conform to identifiers. You do not need to separate a regular identifier when you use it in a Transact-SQL statement.

SELECT *FROM TableXWHERE KeyCol = 124

Delimited identifiers

Enclosed in double quotes (") or square brackets ([]). Identifiers that conform to the format rules for identifiers can be delimited or not delimited.

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

In Transact-SQL statements, identifiers that do not conform to all identifier rules must be delimited.

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

A regular identifier and a delimited identifier must contain a number of characters between 1 and 128. For local temporary tables, identifiers can be up to 116 characters.

The important difference between the two is that the regular identifiers must strictly follow the naming rules, while delimited identifiers can be separated by [].

Identifier format:
　　
1. Identifiers must be characters specified in the Uniform Code (UNICODE) 2.0 standard, as well as some other language characters. such as Chinese characters.
　　
2, the character after the identifier can be (except the condition one) "_", "@", "#", "$" and numbers.
　　
3, the identifier is not allowed to be Transact-SQL reserved words.
　　
4. No spaces and special characters are allowed within the identifier.
　　
In addition, some identifiers that start with a special symbol have a specific meaning in SQL SERVER.

An identifier that begins with "@" indicates that it is a local variable or an argument to a function; an identifier that begins with a # indicates that it is a temporary table or a stored procedure.

The beginning of "# #" indicates that this is a global temporary database object. T

The Ransact-sql global variable begins with "@@".

An identifier can hold up to 128 characters.