MS SQL XML - SQLXML 3.0 SP2 Managed Classes: Passing NULL to a stored Procedure

Author

SQLXML 3.0 SP2 Managed Classes: Passing NULL to a stored Procedure

James Richardson

2005-07-04, 9:23 am

This may sound like a very stupid question, so apologies before i begin.

I have a C# class that is connecting to SQL Server 2000, via the SQLXML
managed classes. There is a method that calls a stored procedure with some
parameters. These parameters are defined in the SProc header with a default
value of NULL. The SProc returns a RETURN_VALUE and/or some XML via a
SELECT . FOR XML AUTO clause.

The SQLXML managed classes use their own type of Command and Parameter
objects, taken from the Microsoft.Data.SqlXml namespace.

The Command and Parameters are setup as follows:

SqlXmlCommand cmd = new SqlXmlCommand(Common
. ConnectionStringOLED
B);

cmd.CommandType = SqlXmlCommandType.Sql;

cmd.CommandText = "EXEC ? = APP_Reports_GetXml ?, ?, ?";

SqlXmlParameter pReturn = cmd.CreateParameter();

SqlXmlParameter pIn = cmd.CreateParameter();

pIn.Name = "@SID";

pIn.Value = "ABC123";

pIn = cmd.CreateParameter();

pIn.Name = "@RPT_ReportId";

Int32 vInt32 = GetParam("ReportId", -1);

if (vInt32 >= 0) pIn.Value = vInt32; else pIn.Value = DBNull.Value;

pIn = cmd.CreateParameter();

pIn.Name = "@RPT_ModuleKey";

String vString = GetParam("ModuleKey", "");

if (vString.Length > 0) pIn.Value = vString; else pIn.Value = DBNull.Value;

The above fails. I have tried various combinations, eg: not setting the
parameter.

How do you pass a NULL to a parameter?

Another issue I have, is getting the Return Value from the SProc.

How do you get the Return_Value from the SProc?

When using the System.Data.SqlClient namespace everything seems to be much
easier and works like a dream. You can use the SqlInt32.Null value, access
the Parameter collection, define a parameter Return_Value, or input and/or
Outputs.

Any clues in what I may be doing wrong, would be most welcome.

Thanks to all,

James.

Monica Frintu [MSFT]

2005-07-13, 1:23 pm

Hi,

Have you tried :

param.Value = null;

This should work for you.

Best regards,
Monica Frintu

------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Monica Frintu [MSFT]

2005-07-13, 1:23 pm

Hello,

Regarding your question about executing an SP, here is an example:

XmlReader Reader;
XmlTextWriter TxtWriter;

Reader = cmd.ExecuteXmlReader();

//Create a new XmlTextWriter instance to write to the console
TxtWriter = new XmlTextWriter(Consol
e.Out);

//Move to the root element
Reader.MoveToContent();

//Write the document to the console
TxtWriter.WriteNode(Reader, false);

//Flush the writer and close the reader
TxtWriter.Flush();
Reader.Close();

Hope this helps,
Monica Frintu

------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Sponsored Links