Explain me this SELECT dbo.TableName(@variable) statement

Question

I'm working on one program that I need to modify a little. There's one SQL statement I don't understand what it does (or basically how it does it).

string query = "SELECT dbo.BusinessMinutes(@start,@end,@priorityid)";

SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.Add("@start", SqlDbType.DateTime).Value = start;
cmd.Parameters.Add("@end", SqlDbType.DateTime).Value = end;
cmd.Parameters.Add("@priorityid", SqlDbType.UniqueIdentifier).Value = priorityId;                    

SqlDataAdapter READER = new SqlDataAdapter();
READER.SelectCommand = cmd;                    

DataTable table = new DataTable();
READER.Fill(table);

if (table.Rows.Count == 1)
{                                             
    minutes = (int)table.Rows[0][0];
}

So can someone explain me the SELECT statement there. The end result (minutes) is as expected so it works but that syntax confuses me. Is this somehow equal to SELECT * FROM dbo.BusinessMinutes WHERE...

Is this commonly used and does this syntax has some special name so I could name my question better? Thank you in advance.

`dbo.BusinessMinutes` is a *User defined function* – Alex K. Apr 04 '14 at 13:33 — Alex K., Apr 04 '14 at 13:33

score 5 · Answer 1 · edited May 23 '17 at 11:57

5

It is not a table name. I think you call into a FUNCTION.

how to create and call scalar function in sql server 2008

has more explanations and examples.

edited May 23 '17 at 11:57

Community

1
1

answered Apr 04 '14 at 13:32

TomTom

61,059
10
88
148

5

This is a Scalar function, not a table function. – Adriaan Stander Apr 04 '14 at 13:33
Corrected. Overlooked the return in the call. – TomTom Apr 04 '14 at 13:34
Yeah. and now i even have a stackoverflow internal link ;) – TomTom Apr 04 '14 at 13:36

Crono · Accepted Answer · 2014-04-04T13:51:33.327

dbo.BusinessMinutes has to be a UDF (User Defined Function) that returns a simple scalar value based on a starting date, an ending date and a priority indicator.

Scalar functions, be it UDF or native, can be used in a SELECT statement to produce a field in the returned resultset. Thus, the code you have is perfectly legal.

For more information about scalar UDF, read this MSDN article.

As a side note, a better implementation for that code would be this:

string query = "SELECT dbo.BusinessMinutes(@start,@end,@priorityid)";

using (SqlCommand cmd = new SqlCommand(query, con))
{
    cmd.Parameters.Add("@start", SqlDbType.DateTime).Value = start;
    cmd.Parameters.Add("@end", SqlDbType.DateTime).Value = end;
    cmd.Parameters.Add("@priorityid", SqlDbType.UniqueIdentifier).Value = priorityId;                    

    // assuming connection is already open
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        if (reader.Read()) minutes = reader.GetInt32(0);
    }
}

If all you want is to init the minutes variable, using SqlDataReader will be more efficient and performant than creating a SqlDataAdapter and a DataTable. The using statements will also make sure your SqlCommand and SqlDataReader objects gets disposed of properly.

Explain me this SELECT dbo.TableName(@variable) statement

2 Answers2