Syntax for Stored Procedure Parameter

Question

I have a value which I am trying to add as a parameter to a stored procedure.

The value of this parameter could either be a STRING of "1" or "NULL"

If it's NULL then I want to add the parameter of DBNull.Value

If it's "1" I want to add the parameter of 1 (int)

I have tried numerous combinations of syntax but cannot get it right.

This is what I have:

cmd.Parameters.Add(new SqlParameter("@LinkClickedId", (insertValuesSplit[12] == "NULL") ? Convert.ToInt16(insertValuesSplit[12]) : DBNull.Value));

But I have a syntax error of

Type of conditional expression cannot be determined because there is no implicit conversion between 'short' and 'System.DBNull' Can anyone suggest what the syntax should be please?

Have you ever read the documentation of [`?:` operator](https://msdn.microsoft.com/en-us/library/ty67wk28.aspx)? It says: _Either the type of first_expression and second_expression must be the same, or an implicit conversion must exist from one type to the other._ — Soner Gönül, Feb 05 '15 at 13:25
By the way, I think you should invert the values assigned to the true/false conditions — Steve, Feb 05 '15 at 13:28

score 1 · Answer 1 · answered Feb 05 '15 at 13:25

Try casting it to an object:

(object) Convert.ToInt16(insertValuesSplit[12])

The problem is that there is no common type that your ternary operator can discern between Convert.ToInt16(insertValuesSplit[12]) and DBNull.Value, but if you cast your value to an object, then it works because there is an implicit conversion between DBNull.Value and object.

score 1 · Answer 2 · answered Feb 05 '15 at 13:26

1

(insertValuesSplit[12] == "NULL") ? Convert.ToInt16(insertValuesSplit[12]) : DBNull.Value)

Short-hand if-statements have to return the same type.

Convert.ToInt16(insertValuesSplit[12]) returns a short

DBNull.Value returns an Object

answered Feb 05 '15 at 13:26

Shelby115

2,816
3
36
52

score 0 · Answer 3 · answered Feb 05 '15 at 13:26

The ternary operator is an expression, and as such it has a type. The compiler tries to determine this type but fails because your true-case is a short but your false-case a DBNull.

You have to explicitly cast one of them to object to make it work, e.g.
(object) Convert.ToInt16(insertValuesSplit[12]).

score 0 · Answer 4 · answered Feb 05 '15 at 13:28

The true and false sub-expressions of your conditional operator have types DbNull and Int16.

You probably need to cast the results to the only common base type: object.

PS. I think you have the condition reversed: if "NULL" you want DBNUll and not the parse.

Syntax for Stored Procedure Parameter

4 Answers4