The difference between an SQL statement with parameters and an SQL statement without parameters.

The SQL statement with parameters is executed on the database.
Exec sp_executesql

Eg:

Exec sp_executesql N'Insert into lcs_sys_model ([model_guid], [model_name], [model_desp], [model_issys]) values (@ model_guid, @ model_name, @ model_desp, @ model_issys )', N' @ model_guid uniqueidentifier, @ model_name nvarchar (50), @ model_desp nvarchar (500), @ model_issys bit', @ Model_guid = 'f3cd1369-58c0-4a1f-af88-05fcf734e079 ', @ model_name = n' test entity model', @ model_desp = n' entity model description', @ model_issys = 0

There are no SQL statements with values. Its execution is a direct SQL statement.
InsertLcs_sys_model ([model_guid], [model_name], [model_desp], [model_issys]) values ('F3cd1369-58c0-4a1f-af88-05fcf734e079 ', 'name', 'dep', 1)

View the preceding executionCodeWill you think it is the efficiency below? To be honest, I have not tested it carefully. However, as far as I know, sqlserver can cache parameter information and compiled SQL information. The first SQL statement with parameter numbers is unchanged (in red ). So it can be cached (just like executing a stored procedure ). The following SQL statement. BecauseValue After valueIt is always not fixed, so sqlserver always considers it a different SQL statement. Therefore, we need to re-compile and generate each time.

 

 

The above conclusions come from the following test code.
Http://space.cnblogs.com/question/2692/

System. Data. sqlclient. sqlparameter [] parameters = {
New sqlparameter ("@ model_guid", sqldbtype. uniqueidentifier ),
New sqlparameter ("@ model_name", sqldbtype. nvarchar, 50 ),
New sqlparameter ("@ model_desp", sqldbtype. nvarchar, 500 ),
New sqlparameter ("@ model_issys", sqldbtype. Bit)
};
Parameters [0]. value = new GUID ("F3CD1369-58C0-4A1F-AF88-05FCF734E079 ");
Parameters [1]. value = "test entity model ";
Parameters [2]. value = "entity model description ";
Parameters [3]. value = false;

String connstr = @ "Integrated Security = sspi; persist Security info = false; initial catalog = officially; Data Source = lcsnb" sql2k ";
String comment STR = "insert into lcs_sys_model ([model_guid], [model_name], [model_desp], [model_issys]) values (@ model_guid, @ model_name, @ model_desp, @ model_issys )";

System. Data. sqlclient. sqlconnection conn = new system. Data. sqlclient. sqlconnection (connstr );
System. Data. sqlclient. sqlcommand cmd = new system. Data. sqlclient. sqlcommand (writable Str );

Cmd. Connection = conn;
Foreach (VAR item in parameters)
{
Cmd. Parameters. Add (item );
}

Cmd. Connection. open ();
Cmd. executenonquery ();
// The first execution ends.
Cmd. Parameters. Clear ();
Cmd. commandtext = "select scope_identity ()";

// System. Diagnostics. Debug. Assert (CMD. executescalar () = NULL );

Console. writeline (CMD. executescalar (). tostring ());
// The second execution ends.
Cmd. Connection. Close ();

Console. writeline ("the first test ends ");
Console. Read ();

Cmd. commandtext = "insert into lcs_sys_model ([model_guid], [model_name], [model_desp], [model_issys]) values ('f3cd1369-bucket', 'name', 'dep ', 1 )";
Cmd. Connection. open ();
Cmd. executenonquery ();

Cmd. commandtext = "select scope_identity ()";
Console. writeline (CMD. executescalar (). tostring ());
Cmd. Connection. Close ();
Console. writeline ("the second test ends ");
Console. Read ();

I used to insert a record with parameterized SQL statements and retrieve the auto-increment value. This execution method is found on the Database End. So there's no drama... Only SQL statements can be changed. Put auto-increment at the end of SQL ..

Exec sp_executesql N'Insert into lcs_sys_model ([model_guid], [model_name], [model_desp], [model_issys]) values (@ model_guid, @ model_name, @ model_desp, @ model_issys)', N'@ Model_guid uniqueidentifier, @ model_name nvarchar (50), @ model_desp nvarchar (500), @ model_issys bit',@ Model_guid = 'f3cd1369-58c0-4a1f-af88-05fcf734e079 ', @ model_name = n' test entity model', @ model_desp = n' entity model description', @ model_issys = 0

Select scope_identity ()

Run it in the query analyzer... These two values are in different ranges and cannot be obtained ..

 

 

 

Finally, we need to use it when writing the Stored Procedure for the sake of performance and security.

Exec sp_executesql'SQL statements with Parameters','Parameter Definition Statement. Multiple parameters are separated by commas (,).',Parameter Value 1, parameter value 2, parameter value 3 '''

To execute our SQL statements, we can see that many paging stored procedures are directly pieced together SQL statements and then executed with exec (''). This is not recommended in terms of efficiency and security.

 

This seems to be my first HomepageArticle. If something is wrong, please kindly advise.