When we call SQL Server's stored procedure in ASP, if we use the Adodb.Command object, we usually use the following code:
dim cmd, rs
set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "TestProc"
cmd.Parameters.Append cmd.CreateParameter("@a" , adInteger, adParamInput, 4, 1)
cmd.Parameters.Append cmd.CreateParameter("@b" , adVarChar, adParamInput, 50, 'b')
...
set rs = cmd.Execute
Today when I was debugging a program, I found that the ASP page indicated that a certain parameter was not assigned a value, but in fact it was another parameter that I did not assign a value to. So I opened the profiler of Sql Server, executed the program, and captured that the SQL statement sent by ASP to Sql Server was actually in the following form:
execute TestProc 1, 'b', ....
The reason is now obvious. However, the ADO engine does not translate the call to the stored procedure into a complete syntax, but uses the above abbreviation method. In this way, when a parameter in the middle is lost, it may be misjudged as another parameter being lost due to misalignment.
Then I checked the properties of the Command object and added the following sentence:
cmd.NamedParameters = true
, which means that I specify to use an explicitly named variable form. Then I executed the program and found that the statement captured in the event profiler became Now:
exec TestProc @a = 1, @b = 'b', ...
the error parameters are also correct.
Everything is OK now