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)


Looking at the Execution Code above, do 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 );
}