Differences between SQL stored procedures with and without parameters page 1/2

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-Beijing ', @ 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.
Insert into lcs_sys_model ([model_guid], [model_name], [model_desp], [model_issys]) values ('f3cd1369-bucket', '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. Because the values following values are always not fixed, sqlserver always considers them as different SQL statements. Therefore, we need to re-compile and generate each time.

The above conclusions come from the following test code.

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