The difference between SQL with parameters and SQL stored procedures without parameters Page 1/2

The SQL statement with parameters is executed on the database side
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 solid model', @Model_Desp = N'Solid model description', @Model_IsSys = 0


There is no SQL statement with value. Its execution is a direct SQL statement.
insert into lcs_Sys_Model ([Model_GUID],[Model_Name],[Model_Desp],[Model_IsSys]) values ( 'F3CD1369-58C0-4A1F-AF88-05FCF734E079','name','desp',1 )


Looking at the above code execution, do you think the following is efficient? To be honest, I haven't tested it carefully. But as far as I know, SQLserver can cache parameter information and SQL compiled information. The first sentence SQL with parameters is unchanged because the previous part of the SQL is unchanged (red part). So it can be cached (just like executing stored procedures). And the following sentence sql. Because the value after the value is always not fixed, sqlserver always considers it to be a different sql statement. Therefore, it is necessary to recompile and generate each time.

The above conclusion comes from the test code below.

[] parameters = {
new SqlParameter("@Model_GUID", ),
new SqlParameter("@Model_Name", ,50),
new SqlParameter("@Model_Desp", ,500),
new SqlParameter("@Model_IsSys", )
};
parameters[0].Value = new Guid("F3CD1369-58C0-4A1F-AF88-05FCF734E079");
parameters[1].Value = "test solid model";
parameters[2].Value = "Solid Model Description";
parameters[3].Value = false;



string connStr = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=officially;Data Source=LCSNB"SQL2K";
string cmdStr = "insert into LCS_Sys_Model ([Model_GUID],[Model_Name],[Model_Desp],[Model_IsSys]) values ( @Model_GUID,@Model_Name,@Model_Desp,@Model_IsSys)";

conn = new (connStr);
cmd = new (cmdStr);

= conn;
foreach (var item in parameters)
{
(item);
}