2

OK, this is a pretty simple problem, but I'm curious: what is the best approach in terms of efficiency and style?

I often need to select data in MS SQL, but am looking for a single result. If there is no match in the database, I'd like to return a default value.

Here are a few approaches I take:

Do it in Code

--Regular Select in SQL
SELECT myValue FROM dbo.SomeTable WHERE id = @id


//In Data Access Layer
using (IDataReader reader = cmd.ExecuteReader()) {
    if (reader.Read()) {
        return Convert.ToInt32(reader["myValue"]);
    }else {
        return 0; //return a default
    }
}

Guarantee a returned value in SQL

--SQL stores in a variable and selects null value if available
declare @myVal int
select @myVal = myValue FROM dbo.SomeTable WHERE id = @id

--return value or a default
select ISNULL(@myVal,0) as myValue


//In Data Access Layer
return Convert.ToInt32(cmd.ExecuteScalar());

Is there a more elegant way to do this? Or a more efficiient way?

If not, which of these would you consider a better practice?

jkelley
  • 2,570
  • 3
  • 21
  • 24
  • As a general rule I'd say that the default should be returned in SQL if you expect all other systems to rely on the existence of the default. If you don't think most other systems will care one way or the other then the default should be applied in the client app. I'm afraid I can't offer anything more specific since I'm not that good with TSQL. – FrustratedWithFormsDesigner Nov 23 '10 at 14:56

6 Answers6

2

I would use ISNULL(field, replacement_value_if_null) its a native T-SQL function and its all done from the SQL Server side so more easy to debug.

If someone has some input in terms of performance, that might be usefull!

il_guru
  • 8,383
  • 2
  • 42
  • 51
byte_slave
  • 1,368
  • 1
  • 12
  • 24
  • 1
    `ISNULL` won't work here: if there are no records, the query returns an empty recordset, not a `NULL` value. – Albireo Nov 23 '10 at 14:52
  • @kappa: is there a way to detect an empty record set, like Oracle's `NO_DATA_FOUND` exception? – FrustratedWithFormsDesigner Nov 23 '10 at 14:55
  • 1
    +1 For pointing out when ISNULL works -> but you could use it in the second part of a query on a variable as shown in the question – jkelley Nov 23 '10 at 14:56
  • @frustratedwithformsdesigner: if(dr["field"] == DBNull.Value) – byte_slave Nov 23 '10 at 15:02
  • @FrustratedWithFormsDesigner: It depends. Using a SQLDataReader? Yes, check `SQLDataReader.HasRows == true`. Using `SQLCommand.ExecuteScalar()`? I don't think, have you tried my suggestion? (I haven't checked it, it's only a guess. :\) – Albireo Nov 23 '10 at 15:04
  • @jkelley: Yes, but that I would use type of code only if I'm already using a stored procedure, creating one only for this reasons seems a waste to me. – Albireo Nov 23 '10 at 15:05
  • @FrustratedWithFormsDesigner: if (reader.Read()) does nothing if there are no records returned. So, it is implicitly checking for an empty resultset. – DOK Nov 23 '10 at 15:08
  • @jkelley: I've update my answer with my test, check if it's enough for you. – Albireo Nov 23 '10 at 15:10
  • @DOK: I think the difference is that Oracle's `NO_DATA_FOUND` can be caught in PL/SQL in the database, not in the client code. That way your stored procedure / function will know if there's no data and can handle it before anything gets returned. – FrustratedWithFormsDesigner Nov 23 '10 at 15:12
0

It is an old post but in case people are still looking for the answer like I was not too long ago:

Why could not you use:

IF(ISNUMERIC(@myVariable) = 1)... If your variable is a numeric.

Or maybe IF(LEN(@myVariable) > 0)... for strings.

Greg
  • 640
  • 5
  • 11
  • 23
0

I'm only guessing here, but: have you tried with this code?

Nullable<Int32> Result = Command.ExecuteScalar();

if (Result == null) Result = 0;

Edit: Now I've checked it, and it works:

using (SqlConnection Connection = new SqlConnection())
{
    Connection.ConnectionString = "";
    Connection.Open();

    using (SqlCommand Command = Connection.CreateCommand())
    {
        Command.CommandText = "select 'lol' as [column] where 1 = 0";

        String Result = (String) Command.ExecuteScalar();
    }
}

String is null. So you can use Nullable and you're done.

Albireo
  • 10,977
  • 13
  • 62
  • 96
0
((int?)cmd.ExecuteScalar()).GetValueOrDefault();

or

((int?)cmd.ExecuteScalar())??0;

Is what you want I guess

Arsen Mkrtchyan
  • 49,896
  • 32
  • 148
  • 184
  • @DOK why you remove second part of my answer? – Arsen Mkrtchyan Nov 23 '10 at 15:00
  • I didn't remove anything. Only the first code snippet was there when I added the code formatting to improve readability. Perhaps we were both editing at the same time? You can look it up in history. – DOK Nov 23 '10 at 16:30
0

It doesn't seem to me that there is much difference between returning a NULL or returning a zero. Either way, your code still has to do something different than it would do if a result was returned. You're still going to have an if-else. If a record is returned, do this, else do some other thing. So I don't really see much difference.

I would encourage you not to use a DataReader to return a single value. It would be better to use ExecuteScalar, or for better performance, an output parameter from a stored procedure.

DOK
  • 32,337
  • 7
  • 60
  • 92
  • Good advice on ExecuteScalar - as far as differing behavior based on NULL or a value - the intent is to provide default values in a situation like Delivery Area Surcharges for shipping. I'm querying to see if there is a match based on zip code. If not (No Result Returned), then the surcharge is zero, so the system behaves consistently in both cases. – jkelley Nov 23 '10 at 15:08
  • @jkelley If you hard-code the default value in the stored procedure or in your code, it could be quite difficult for someone else to find and change it in the future. I wonder if it would be possible to put the default value in the database and return it instead of, or in addition to, the requested value. That would make the default value easier to locate and maintain. – DOK Nov 23 '10 at 16:28
-1

I am using SQL SERVER 2008 R2. I had the same task to accomplish (return some default value when no real value available) and I used statement below. The result is the default value, but if I would add 'Florida' instead of 'English' - it will return 'FL' as state abbreviation:

--- declare and give default value: declare @myVal varchar(10)= 'No result' select @myVal = StateAbbr FROM dbo.State WHERE StateName = 'English'

--return value or a default select @myVal

Miriamka
  • 11