Are stored procedures executed from text string?

Question

There are several SQL servers having stored procedures, for example Microsoft SQL Server or PostgreSQL. There are also several client side objects implementing stored procedures calls (TADOStoredProc in Delphi, SqlCommand in .NET Framework etc.).

The question I always wanted to ask is:

are stored procedures executed always in special efficient way with binary representation of their parameters or are the super-advanced objects which represent stored procedures' parameters always converted to a plain text string and stored procedure is always executed by sending this plain text string to SQL server? (Let's take one technology for example - let it be SQL Server and ADO.NET).

I noticed that for ADO.NET procedure's parameter names do not have any meaning - only their creation order is important which makes me think about an idea with plain text string.

Update for @Alex K.

I've tested following code in .NET:

CREATE PROCEDURE paramtest
@par1 nvarchar(50),
@par2 nvarchar(50),
@par3 nvarchar(50)
AS
  SELECT Res = '@par1 = ' + @par1 + '; @par2 = ' + @par2 + '; @par3 = ' + @par3
  RETURN 555

using System;
using System.Data.SqlClient;
using System.Data;

namespace SqlParamTest
{
    class Program
    {
        private static void addParam(SqlCommand cmd, string parameterName, ParameterDirection direction, SqlDbType dbType, int size, object value)
        {
            SqlParameter par = new SqlParameter(parameterName, dbType, size);
            par.Direction = direction;
            par.Value = value;
            cmd.Parameters.Add(par);
        }

        static void Main(string[] args)
        {
            using (SqlConnection conn = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=test;Integrated Security=True"))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "paramtest";
                addParam(cmd, "@par3", ParameterDirection.Input, SqlDbType.NVarChar, 50, "third");
                addParam(cmd, "@par2", ParameterDirection.Input, SqlDbType.NVarChar, 50, "second");
                addParam(cmd, "@par1", ParameterDirection.Input, SqlDbType.NVarChar, 50, "first");
                addParam(cmd, "@Return", ParameterDirection.ReturnValue, SqlDbType.Int, 0, null);
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (rdr.Read()) Console.WriteLine((string)rdr["Res"]);
                rdr.Close();
                Console.WriteLine("Return value: {0}", cmd.Parameters["@Return"].Value);
            }
            Console.ReadKey();
        }
    }
}

and yes, it maintains parameters in a right way, but I think, it is .NET who adds additional checks to parameters, because following code in Delphi:

procedure TMyClass.Test(Conn: TADOConnection);
var SP:TADOStoredProc;
begin
  SP := TADOStoredProc.Create(nil);
  try
    SP.Connection := Conn;
    SP.ProcedureName := 'paramtest';
    SP.Parameters.CreateParameter('@whatthehell', ftString, pdInput, 50, 'one');
    SP.Parameters.CreateParameter('@AnotherCrap', ftString, pdInput, 50, 'two');
    SP.Parameters.CreateParameter('?', ftString, pdInput, 50, 'three');
    SP.ExecProc;
  finally
    SP.Free;
  end;
end;

returns:

@par1 = one; @par2 = two; @par3 = three

and doesn't complain about missing parameters.

pdReturnValue works only if this parameter is created before any other parameters.

score 1 · Accepted Answer · edited May 23 '17 at 11:43

Not sure what answer your looking for, stored procedure command text & parameters are passed to the driver/provider or natively via ADO.NET which formats it as a TDS (tabular data stream) RPC (remote procedure call) Message which is then passed to the server over which ever network protocol is being used; pipes, tcp/ip et al. The data is sent in a binary stream.

The TDS spec is available from Microsoft if your interested.

SQLCommand Stored Procedure calls do need a parameter name, its OleDB/ODBC that only care about the order and use ? as the parameter placeholder rather than @NAME.

Regarding order

In your example the order is not relevant because you are providing the server with the correct names for the params, so this is what is sent to the server:

exec paramtest @par3=N'third',@par2=N'second',@par1=N'first'

This is enough information for the server to figure out the correct params/order.

If you changed to

addParam(cmd, "@xxpar3",
addParam(cmd, "@xxpar2", 
addParam(cmd, "@xxpar1",

The server would detect that it does not have a param named xxxpar* and fail with a "missing @par1" error.

If you modified addParam so it did not set paramater names .net will create defaults:

exec paramtest @Parameter1=N'third',@Parameter2=N'second',@Parameter3=N'first'

Which would cause the above error.

If you modified addParam so it did not set paramater names and then overwrite the automatic ones;

cmd.Parameters.Add(par);
par.ParameterName = "";

This is what gets executed:

 exec paramtest N'third',N'second',N'first'

resulting in

 @par1 = third; @par2 = second; @par3 = first

I have no idea what Delphi does ... The full version of SQL Server ships with a tool called SQL Profiler which displays the textual data that gets sent to the server instance so you can see exactly whats going on. What profiler to use with sql express?

Are stored procedures executed from text string?

1 Answers1