Databases
StackOverflow2013
Postsdbo
POIs there a way I can send a NULL value to a SQL stored proc through XML?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there a way I can send a NULL value to a SQL stored proc through XML?
    text
    copied!<p>I'm wondering if there is a way I can send a NULL or DBNull.Value from my C# data service to a stored proc through some configuration xml parameter.</p> <p>In the proc, I want to pull some values out of the xml as a bit but because the UI allows for a third state, the bit value coming in from the xml can be NULL in which case I want to ignore any SQL updates for that field.</p> <pre><code> XElement xml = new XElement("XML"); xml.Add(new XElement("SomeConfigValue", NULL)); </code></pre> <p>It seems when I throw a NULL into the xml from the C#, it defaults to a node with no value.</p> <pre><code>&lt;SomeConfigValue /&gt; </code></pre> <p>When I try to grab the value from the xml as a bit in the SQL the value is interpreted as a 0. </p> <pre><code>select isnull(@Configuration.value('/Configuration[1]/SomeConfigValue [1]', 'bit'), NULL) </code></pre> <p>That's all fine and good. It makes sense. I'm just curious if anyone can suggest ideas from which I can get this to work the way I'd like it to. Perhaps I can't rely on the SQL bit type but I am currently re-factoring to be strongly typed.</p> <p>I'd like to be able to grab a NULL if the value coming in isn't a 1 or 0.</p> <pre><code>set @SomeConfigValue = isnull(@Configuration.value('/Configuration[1]/SomeConfigValue [1]', 'bit'), NULL) </code></pre> <p>The goal is to ignore updating that value if it is NULL, or not a 1 or 0 in this specific SQL bit type case.</p> <pre><code> update MyTable set ConfigValue = case when @SomeConfigValue IS NULL then T.SomeConfigValue else @SomeConfigValue end from SomeTable T </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload