// class is public
public class Arithmetic
{
// method is static and public
// but this method and the one that follows it
// have the same name so neither can be used for
// the implementation of a procedure, function, or index
public static int AddTheseNumbers(int a1, int a2)
{
return a1 + a2;
}
// method is static and public
public static int AddTheseNumbers(int a1, int a2, int a3)
{
return a1 + a2 + a3;
}
}

// class is public
public class Arithmetic
{
public class Numeric
{
// Invert may not be used to implement
// a procedure, function, or trigger
// because it is a member of a nested type
public static int Invert(int a1)
CLR METHODS IN SQL SERVER 59
30610 03 pp. 053-094 r1ah.ps 4/26/04 6:46 PM Page 59
{
Return –a1;
}
}
}


// 3-6
public class CPulley
{
public static SqlDouble PulleyDistance(
SqlDouble Pulley1Diameter,
SqlDouble Pulley2Diameter,
SqlDouble BeltLength)
{
if(Pulley1Diameter.IsNull ||
Pulley2Diameter.IsNull ||
BeltLength.IsNull)

p63

public class Limits
{
// SqlDouble and SqlInt32 used as parameters
public static int Edge(SqlDouble d1, SqlInt32 i2)
{
// gets SqlDouble value as CLR double
// so its square root can be calculated
if(System.Math.Sqrt(d1.Value) > 3.0)
{
return 7;
}
// gets SqlInt32 value as CLR int
SYSTEM.DATA.SQLTYPES 63
30610 03 pp. 053-094 r1ah.ps 4/26/04 6:46 PM Page 63
// so it can be returned as an int
int i = i2.Value;
return i;
}
}

namespace Math
{
public class Arithmetic
{
public static int SameAs1(SqlInt64 i)
{
// this compiles and works as expected
// it coerces the SqlInt64 to a Int32
// instead of long, which corresponds to SqlInt64
return (int)i;
}
public static int SameAs2(SqlInt64 i)
{
// this produces a compile-time error
// because Value is a long
return i.Value;
}
}
}

SqlString s = new SqlString("1.01");
SqlMoney m = (SqlMoney)s;
// this line prints out 1.01
Console.WriteLine(m);

SqlString s2 = "{90110409-6000-11D3-8CFE-0150048383C9}";
SqlGuid g = (SqlGuid)s2;
// this line prints out 90110409-6000-11D3-8CFE-0150048383C9
Console.WriteLine(g);


public class Math
{
public static SqlInt64 Same3(SqlInt32 i)
{
// i is implicitly cast to SqlInt64
// because that is what is required
// for the return value
return i;
}
}
Another example of implicit coercions follows.
SqlDecimal sdecimal = new SqlDecimal(1.0);
SqlDouble sdouble = 1.0;
// the following line does an explicit conversion
sdecimal = (SqlDecimal)sdouble;
// the following line does an implicit conversion
// not that it does not require (SqlDecimal)
Sdecimal = sdouble;
// the following attempts an implicit conversion but will
// not work because there is no implicit conversion
// from SqlDouble to SqlDecimal
sdecimal = sdouble; // compile-time error



SqlDouble sdouble = new SqlDouble(1.0);
// no explicit conversion is required because 3.0
// is a double and the type of the Value property
// of SqlDouble is double


// sets the Value of si to 12
SqlInt32 si = 12;
// this will produce a compile-time error
// because there is no implicit coercion from
// SqlInt32 to int
int i = si;
// you must use explicit coercion
int i1 = (int)si;

double d = double.Nan;
// this will cause a runtime error
SqlDouble sd = d;
d = 4.7;
// this will work as expected
SqlDouble sd2 = d;

// p68
public SqlInt32 A = new SqlInt32(3);
public SqlInt32 B = new SqlInt32(5);
// this uses op_Addition to add A and B

// p69
SqlDecimal sd = SqlDecimal.MaxValue;
// this causes a conversion and produces a runtime error


// p70
SqlSingle s = 4.5F;
SqlDouble d = 4.5F;

// the following lines will compile without error
// but will cause a runtime error
Console.WriteLine(d.CompareTo("asdfasdf"));
// this will cause a runtime error too
Console.WriteLine(d.CompareTo(4.5));
// and so will thisConsole.WriteLine(d.CompareTo(s));
// this will work as expected
Console.WriteLine(d.CompareTo((SqlDouble)s);
decimal d = sd.Value;
public SqlInt32 Sum = A + B;
sdouble = 3.0;
return SqlDouble.Null

//p71
SqlBoolean b = new SqlBoolean(false);
// this will produce a compile-time error
if(b == null)
{
Console.WriteLine("b is null");
}

//The IsNull property has to be used to check whether an instance of a
//SqlType is representing a null. The following code fragment illustrates the
//proper way to check a SqlType for null.
SqlBoolean b = new SqlBoolean(false);
// this will check to see if b is null
if(b.IsNull)
{
Console.WriteLine("b is null");
}

//p72
// the variable b is not null
SqlBoolean b = new SqlBoolean(false);
// this will check to see if b is null
if(b.IsNull)
{
This code will not execute
Console.WriteLine("b is null");
}
// this assigns a null to b
b = SqlBoolean.Null;
if(b.IsNull)
{
This code will execute
Console.WriteLine("b is null");
}

SqlInt32 i1 = new SqlInt32(3);
SqlInt32 i2 = new SqlInt32(3);
// this will produce a compile-time error
bool b = i2 == i2;

//p73
SqlInt32 i1 = new SqlInt32(3);
SqlInt32 i2 = new SqlInt32(3);
// this will compile and behave as expected even
// though the result of the comparison is SqlBoolean
if(i1 == i2)
{
// this will be executed
Console.WriteLine("i1 == i2")
}


SqlInt32 i1 = new SqlInt32.Null;
SqlInt32 i2 = new SqlInt32.Null;
if(i1 == i2)
{
// this will be never executed
Console.WriteLine("i1 == i2")
}
if(i1 != i2)
{
// this will be never executed
Console.WriteLine("i1 != i2")
}

//p73-74
SqlInt32 i1 = new SqlInt32.Null;
SqlInt32 i2 = new SqlInt32.Null;
if(i1.Equals(i2))
SYSTEM.DATA.SQLTYPES 73
30610 03 pp. 053-094 r1ah.ps 4/26/04 6:46 PM Page 73
{
// this will be executed
Console.WriteLine("i1 Equals i2")
}
if(i1.ComparesTo(i2) == 0)
{
// this will be executed
Console.WriteLine("i1 Compares To i2")
}

string str = null;
// this will cause a runtime error
// because of a null reference
Console.WriteLine(str.Length);

SqlInt64 i64 = SqlInt64.Null;
// this will cause a runtime error
long l1 = i64.Value;
// this will cause a runtime error too
l1 = (long) i64;

Int32 i32 = SqlInt32.Null;
// this will compile and work as expected
SqlInt64 i64 = i32;
I64 = SqlInt64.Null;
// this will compile and work as expected
i32 = (SqlInt32)i64;

//p76
namespace Procedures
{
public class Parameters
{
// SqlString is used as the type for str
// because it is a SQLType for nchar
// in Table 3-1
public static int PassChars(SqlString str)
{
// the T-SQL LEN function trims trailing
// spaces in the string, so you must too
return str.Value.TrimEnd().Length;
}
}
}

//p77
namespace Procedures
{
public class Parameters
{
// SqlString is used as the type for str
// because it matches the nchar type
// listed in Table 3-1
public static int PassChars(SqlString str)
{
// test for null
if(str.IsNull)
{
// return a 0 just like the PassCharsTSql does
return 0;
}
// the T-SQL LEN function trims trailing
// spaces in the string, so you must too
return str.Value.TrimEnd().Length;
}
}
}

//csc /target:library procedures.cs
//sqlcmd –d Chapter3 –Q "CREATE ASSEMBLY procedures FROM %CD%\procedures.dll with PERMISSION_SET = SAFE"

//p79
public static void PassBack(SqlInt32 valueIn, ref SqlInt32
valueRef)
{
// pass in + out to out
valueRef += valueIn;

//80
public static void PassBack2(int valueIn, ref int valueRef)
{
// pass in + out to out
valueRef += valueIn;
}

//p81

static public SqlString Repeat(SqlInt32 i, SqlString c)
{
return new String(c.Value[0], i.Value);
}

//p83
//
//Two of the properties of SqlFunctionAttribute are
//IsDeterministic and IsPrecise. For example, the Repeat function we
//used at the beginning of this chapter can be rewritten as follows.
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
static public SqlString Repeat(SqlInt32 i, SqlString c)
{
return new String(c.Value[0], i.Value);
}
//A method is considered deterministic if for a given set of inputs it
//always returns the same value. A method that meets this criterion should
//set IsDeterministic=true; otherwise, it should be false. Obviously, the
//Repeat method meets this criterion. The following code is an example of a
//method that is not deterministic.
[SqlFunction(IsDeterministic = false, IsPrecise = true)]
static public SqlGuid NewId()
{
return new SqlGuid(Guid.NewGuid());
}

//The Repeat function does not use System.Single or System.Double, so it
//is precise. The following code is an example of a method that is not precise.
[SqlFunction(IsDeterministic = true, IsPrecise = false)]
static public SqlInt32 Add3(SqlInt32 i)
{
double d = 3.0;
return i + (int)d;
}

public SqlDecimal Value()
[return: SqlFacet(Precision=9, Scale=12)]
{
// implementation
}