SQL stitching string (variable in string) reproduced

Http://blog.csdn.net/sikaiyuan2008/article/details/7848926sql Server database Storage sql

SQL Server stitching strings (variables in strings) is always forgotten, reproduced to help memory.

First, stitching the string (the entire string is not split) step:

First, the string is preceded by a single quotation mark;

The variables in the string are denoted by "[email protected]+" in the string;

If there is a type conversion error at execution time, the appropriate type conversion function is applied and the variable is type-converted (such as the cast () function).

Examples all use the Northwind database.

Example one:

A stored procedure that contains SQL stitching strings:

Create Procedure Test
@TestID int
As
Declare @s nvarchar (800)
Set @s= ' Select * FROM dbo. Categories where categoryid= ' +cast (@TestID as varchar) + "
Print @s
EXEC (@s)

Perform:

EXEC Test @TestID =1

Execution Result:

Second, stitching string (string segmentation) Step:

Enclose strings that do not contain variables in single quotes before and after them.

Concatenation of strings and variables without variables with +

The variable is represented by "'[email protected]+ '" (@para is the variable name);

If a type conversion error occurs while executing the stored procedure, the conversion is performed with the appropriate type conversion function.

The sample takes the Northwind database.

Example two:

Stored procedures that contain SQL strings:

Create Procedure Test
@TestID int
As
Declare @s nvarchar (800)
Set @s= ' Select * FROM dbo. Categories where categoryid= ' + ' +cast (@TestID as varchar) + "

Print @s
EXEC (@s)

Perform:

EXEC Test @TestID =1

Execution Result:

SQL stitching string (variable in string) reproduced