INSERT EXEC from DbCommand

Question

I'm trying to execute the results of a stored procedure that takes parameters into a temporary table.

// Create #temptable
// ..

using (DbCommand command = connection.CreateCommand())
{
    command.CommandText = "INSERT #temptable EXEC [MystoredProcThatHasParams]";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(someObject)
    command.ExecuteNonQuery();
}

Output:

Could not find stored procedure ''.

If I remove command.CommandType = CommandType.StoredProcedure, I get:

Procedure or function 'MystoredProcThatHasParams' expects parameter '@p1' which was not supplied

Is it possible to save the output of a stored procedure that takes parameters from a query in C#?

I assume you're missing a string.Format or something in there? — Michael Edenfield
– Michael Edenfield, Commented Mar 31, 2015 at 23:06

Michael Edenfield · Accepted Answer · 2015-04-01 01:10:00Z

1

The command type StoredProcedure uses a special, higher-performance method for connecting to SQL Server (an RPC call), which requires that the command text be exactly the name of a stored procedure. You cannot include Transact-SQL in the command text if you want to use CommandType.StoredProcedure.

Instead, you need to use CommandType.Text and embed the parameters into the SQL string yourself:

cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT #temptable EXEC [MystoredProcThatHasParams] @Param1, @Param2";
cmd.Parameters.Add("@Param1", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("@Param2", SqlDbType.VarChar, 100).Value = "Test";

edited Apr 1, 2015 at 1:10

answered Mar 31, 2015 at 23:09

Michael Edenfield

28.4k6 gold badges90 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Chamberlain Over a year ago

Your last statement is incorrect. It would only be true if the table was created inside this command, for example from a select * into #temptable from Foo where Foo.Bar = @bar, but INSERT requires the table to pre-exist and this command will not affect its existence. #temp style tables exist for as long as the connection is open or you leave the stored procedure it was created in (if it was created inside a stored procedure)

Scott Chamberlain Over a year ago

If the connection that created the table is the same connection that has the executed command there is no need to worry about connection pooling, the statement "they are reset between SQL calls" is just pain wrong. The connection is only returned to the pool and has its state reset when it is closed. From the OP's example I think it is fairly safe to assume that // Create #temptable uses the same connection object and connection was not closed between the two steps.

Collectives™ on Stack Overflow

INSERT EXEC from DbCommand

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related