SQL Column Names

I wanted to ensure that all my DB columns had been defined consistently.

Ray gave me this simple query to run on INFORMATION_SCHEMA.COLUMNS:

SELECT
   TABLE_NAME,
   COLUMN_NAME,
   DATA_TYPE,
   CHARACTER_MAXIMUM_LENGTH,
   NUMERIC_PRECISION,
   NUMERIC_SCALE,
   CHARACTER_SET_NAME
FROM
   INFORMATION_SCHEMA.COLUMNS
ORDER BY
   COLUMN_NAME

Null values passed via XML into a Stored Proc

If the XML that gets into your proc looks something like this:

'<ArrayOfFeeSaveRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance>
...
...
<RunNumber xsi:nil="true">

Then you will not get nulls in the output from SELECT on the prepared document.
You have to add '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>’ onto the sp_xml_preparedocument command

For example:

EXEC sp_xml_preparedocument @XMLDoc OUTPUT,@XMLOtherFees,'<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'

Then in your WITH you need something like this:

WITH  (
                        FeeID INT,
                        StartDate DATETIME,
                        EndDate DATETIME 'EndDate[not(@xsi:nil = "true")]',   --ALLOW NULLS!!!!!!!!!!!
                        RecurringOrSingleFlag BIT,
                        TransactionTypeID INT,
                        IsRepresentedFee BIT,
                        HostFeeID NVARCHAR(40),
                        Amount DECIMAL(18,4),
                        Comments NVARCHAR(128),
                        DirectDebitID INT
                  )

ExecuteScalar

A tip from Si:
I’m not sure if this has been mentioned before BUT did you know that the ExecuteScalar method on an SqlCommand will cheerfully truncate any string returned through it? That means that XML returned from a stored procedure say may or may not be cut in half depending on its size. The solution is to use the ExecuteXmlReader method instead. Details in this link ExecuteScalar truncates

SQL Output Parameter

I don't why but any SQL Parameters that are defined as outputs are not available until the SqlDataReader has been closed.
So this works:

SqlParameter reservationNumberParameter = new SqlParameter("@ReservationNumber", SqlDbType.NVarChar, 15);
reservationNumberParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(reservationNumberParameter);

reader = command.ExecuteReader();

reader.Close();

reservationNumber = Convert.ToString(reservationNumberParameter.Value);

Whereas this does not work:

SqlParameter reservationNumberParameter = new SqlParameter("@ReservationNumber", SqlDbType.NVarChar, 15);
reservationNumberParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(reservationNumberParameter);

reader = command.ExecuteReader();

reservationNumber = Convert.ToString(reservationNumberParameter.Value);

reader.Close();