SqlClient Xml Output parameter "was not supplied"
I receive an SqlException when running the following code.
"Procedure or function 'usp_Search' expects parameter '@pxmlSearchOutput', which was not supplied."
My parameters + request.
using (var connection = new SqlConnection(_connectionString))
{
using (var command = new SqlCommand("Search.usp_Search", con))
{
var pxmlSearchOutput = new SqlParameter();
pxmlSearchOutput.ParameterName = "@pxmlSearchOutput";
pxmlSearchOutput.SqlDbType = SqlDbType.Xml;
pxmlSearchOutput.Direction = ParameterDirection.Output;
pxmlSearchOutput.Size = 1;
command.Parameters.Add(pxmlSearchOutput);
var pxmlSearchInput = new SqlParameter();
pxmlSearchInput.ParameterName = "@pxmlSearchInput";
pxmlSearchInput.Value = requestXML;//is valid xml, is a local var
pxmlSearchInput.SqlDbType = SqlDbType.Xml;
command.Parameters.Add(pxmlSearchInput);
var pbitDebug = new SqlParameter();
pbitDebug.Value = false;
pbitDebug.ParameterName = "@pbitDebug";
pbitDebug.SqlDbType = SqlDbType.Bit;
com开发者_如何学Gomand.Parameters.Add(pbitDebug);
var pintErrorNumber = new SqlParameter();
pintErrorNumber.ParameterName = "@pintErrorNumber";
pintErrorNumber.SqlDbType = SqlDbType.Int;
pintErrorNumber.Direction = ParameterDirection.Output;
command.Parameters.Add(pintErrorNumber);
connection.Open();
command.ExecuteScalar();
connection.Close();
}
}
Using sql profiler, I can extract the following:
declare @p3 xml
set @p3=null
declare @p4 xml
set @p4=convert(xml,'***Redacted - This is however, valid xml, which convert works on happily***')
declare @p6 int
set @p6=NULL
exec
sp_executesql
N'Search.usp_Search',
N'@pxmlSearchOutput xml output,@pxmlSearchInput xml,@pbitDebug bit,@pintErrorNumber int output',
@pxmlSearchOutput=@p3 output,
@pxmlSearchInput=@p4,
@pbitDebug=0,
@pintErrorNumber=@p6 output
select @p3, @p6
I am unable to diagnose exactly what is wrong with the SQL (and thus, how it relates to the .net code). Any ideas?
You execute the batch, a request of type Text:
Search.usp_Search
You do pass a bunch of parameters to this batch, but those are all ignored as they aren't actually used in the batch itself. You have two alternatives:
- use the parameters passed to the batch in the batch itself:
var command = new SqlCommand("exec Search.usp_Search @pxmlSearchOutput output, @pxmlSearchInput,@pbitDebug, @pintErrorNumber output", con))
- Tell ADO.NEt that you are making an RPC call, not merely executing a batch:
command.CommandType = CommandType.StoredProcedure;
Either change will work (but not both).
精彩评论