PROCEDURES AND FUNCTIONS IN .NET LANGUAGES A method

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES A method is considered precise if its return value does not depend on any calculations that involve System.Single (FLOAT in SQL) or System.Double (REAL in SQL). A method that meets this criterion should set IsPrecise=true; otherwise, it should be false. The reason for this criterion is that typically the hardware in the processor implements arithmetic calculations involving System.Single or System.Double types. Different processors may produce slightly different results for the same calculations. This is not true for calculations involving SqlDecimalor System.Decimal. The Repeatfunction does not use System.Singleor 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; } Scalar-valued functions may be implemented by methods. They are similar to procedures, but they can return any scalar value or a user- defined type. Scalar-valued functions can be used in queries, computed columns, and indexes. For these indexes to work properly, the method that implements the function must be properly decorated with the Sql FunctionAttributeattribute. Functions that return SqlTypes and some others sometimes must specify some extra information that is specific to the type. For example, a SqlDecimal has a precision and scale associated with it. Although an instance of a SqlDecimal has a MaxPrecision and a MaxScale field, the type definition does not. The SqlFacetAttribute is used to add information about the SQL characteristics of the return value of a function. The SqlFacetAttribute has five properties: IsFixedLength, MaxSize, Precision, Scale, and IsNullable. Methods should use the appropriate properties, as shown in the following example. public SqlDecimal Value() [return: SqlFacet(Precision=9, Scale=12)] { // implementation } The SqlFacetAttribute is meant to be used only for return values. Table 3-6 shows the appropriate properties to use for the various SqlTypes.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

SCALAR-VALUED FUNCTIONS Table 3-5: Index and Persisted Column

SCALAR-VALUED FUNCTIONS Table 3-5: Index and Persisted Column Usage of Function IsDeterministic IsPrecise Peristed Column OK? Index OK? Indexed View OK? T T Y Y Y* T F Y Y* Y* F T N N N F F N Y N stores the data for the view so that it does not have to run a query when it is selected. The IsDeterministic and IsPrecise properties of a function must be properly set. For a function written exclusively in T-SQL, this is not a problem, because SQL Server will calculate what these settings should be. Methods that are used to implement functions must be decorated with the SqlFunctionAttribute, or both IsDeterministic and IsPrecise will be false. 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 Repeatmethod 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 NewId function is not deterministic, because each time the static method Guid.NewGuid()is called, it produces a different GUID.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES CREATE TABLE

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES CREATE TABLE R1 ( Id CHAR(1), Other AS dbo.Repeat(3, Id) ) INSERT INTO R1 VALUES ( A ) SELECT * FROM R1 GO A AAA The value of the Other column is just the character in the first column, repeated three times. Although this is not a very useful use of a computed column, it does illustrate how they work. Because a function can be used in a query or a column definition, SQL Server needs to know a few extra things about it. What would happen if the Repeat function were modified so that it repeated the character a random number of times, but not more than three? Each time you did a select from the R1 table, you could potentially see a different number of characters in the Other column. There is nothing intrinsically wrong with this behavior; some built-in functions in SQL Server give different results each time you call them. Suppose further that an index was built on the Othercolumn, with the random Repeatfunction. Would that make any sense? The index wouldn t be very useful, because it would only have a snapshot of what was in the column when it read it, so selects using it wouldn t work. By default, SQL Server assumes that a function based on a CLR method cannot be used to build an index, and will produce an error if you try to make one. Every function in SQL Server has two properties that are associated with it that SQL Server uses to decide if that function can be used in an index: IsDeterministic and IsPrecise. By default, a function based on a CLR method has both of these properties as false. Besides using a computed column as in index, you can also make it PERSISTED. When you make a computed column PERSISTED, it is calculated only once, and the resulting value is stored and then retrieved whenever it is needed. A PERSISTED computed column takes more space than one that isn t, but makes selects run faster. Obviously, a computed column that returned a different value every time you read it would not be any more useful as a PERSISTED column than as an index. Table 3-5 shows how SQL Server decides whether or not to allow a computed column to be PERSISTEDor used in an index. The Y* indicates the computed column involved must be PERSISTEDor the index will not be allowed. An indexed view is special kind of view that

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

SCALAR-VALUED FUNCTIONS Scalar-Valued Functions A scalar-valued function in

SCALAR-VALUED FUNCTIONS Scalar-Valued Functions A scalar-valued function in SQL Server has input parameters, just as a procedure does, but those parameters can only be passed in by value. In addition, it returns a scalar value, not a return code. The return value can be of any of the types that input parameters can use. The following code is an example of a simple function written in T-SQL. CREATE FUNCTION RepeatSql (@repeat INT, @str NCHAR(1)) RETURNS NCHAR(30) AS BEGIN RETURN Replicate(@str, @repeat) END Repeattakes as input parameters an intand a character, and returns a string with the character repeated. The following SQL batch uses it. PRINT dbo.RepeatSql(5, A ) GO AAAAA Using Table 3-1 as a guide, as we did for building the PassCharsprocedure, we can write a method that duplicates the RepeatSql function, which is shown in the following code fragment. static public SqlString Repeat(SqlInt32 i, SqlString c) { return new String(c.Value[0], i.Value); } The Repeat method is not doing the error checking it should just for the sake of keeping the example small. The syntax for adding a function is similar to that for adding a procedure except that it requires an additional piece of information, the return type. The following code is a SQL batch for creating a function from the Repeatmethod. CREATE FUNCTION Repeat(@i INT, @c NCHAR(1)) RETURNS NCHAR(100) EXTERNAL NAME functions.[Chapter3.Functions].Repeat In spirit, the difference between procedures and functions is that a function returns a value that can be used as part of a query, a computed column, or an index. There is no way to use a procedure directly in a query or a computed column. The following SQL batch shows and uses a table that includes a computed column that uses the Repeatfunction.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES T-SQL procedures

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES T-SQL procedures may have default values for parameters, and this is true for procedures implemented with methods. In addition, a single method may be used to implement more than one procedure. This makes it possible to have several procedures, each with a different default value for a parameter, all implemented by the same CLR method. The following SQL script creates the PassBack3 and PassBack4 procedures, both implemented with the PassBackmethod, using a default value for valueIn. CREATE PROC PassBack3(@valueIn INT = 5, @valueOut INT OUTPUT) as EXTERNAL NAME procedures.[Procedures.Parameters].PassBack CREATE PROC PassBack4(@valueIn INT = 7, @valueOut INT OUTPUT) as EXTERNAL NAME procedures.[Procedures.Parameters].PassBack The following SQL script executes the PassBack3 procedure, using a default parameter. DECLARE @i int SET @i = 4 EXEC PassBack3 DEFAULT, @i OUTPUT PRINT @i GO 9 CLR types that correspond to a SqlType(see Table 3-1) can also be used as types for parameters in a stored procedure. The following code is a different version of the PassBack method, rewritten to make use of int instead of SqlInt32. public static void PassBack2(int valueIn, ref int valueRef) { // pass in + out to out valueRef += valueIn; } In summary, procedures implemented using a CLR language can be used in the same way as procedures implemented using T-SQL. They can have parameters passed by value or reference and passed by position or name. A parameter can have a default value, and the same CLR implementation can be used for more than one procedure, each having different default values. CLR implementations that use SqlType parameters must be sure to check for parameters values that are representing null.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

PROCEDURES by value and another passed by reference.

PROCEDURES by value and another passed by reference. Note that this method does not check whether any of the parameters are representing null, just to keep the example short. public static void PassBack(SqlInt32 valueIn, ref SqlInt32 valueRef) { // pass in + out to out valueRef += valueIn; } The T-SQL signature uses the OUTPUT keyword to indicate a parameter passed by reference. If no OUTPUTkeyword is used, the parameter is passed by value. If we assume the preceding method was added to the previous assembly, the CREATE PROCEDUREcommand to create it follows. CREATE PROCEDURE PassBack(@valueIn INT, @valueOut INT OUTPUT) as EXTERNAL NAME procedures.[Procedures.Parameters].PassBack Once this procedure has been added to the database, it can be used as is shown in the following SQL batch. DECLARE @i INT SET @i = 4 EXEC PassBack 3, @i OUTPUT PRINT @i GO 7 Note that a ref parameter in a method can be used to either pass a value in or get a value back or both. In this case, @i was initialized to 4, 3 was added to it and passed back, resulting in 7. Procedures implemented with the CLR work the same as T-SQL procedures. For example, in a T-SQL procedure, parameters can be passed by position, as is shown in the preceding SQL batch, or passed by name. The following code is the preceding batch rewritten to pass parameters by name. DECLARE @i INT SET @i = 4 EXEC PassBack @valueRef = @i OUTPUT, @valueIn = 3 PRINT @i GO 7 Parameters passed by name must use the name used in the CREATE PROCEDUREstatement, not the names used in the CLR method.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Once the

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Once the assembly has been cataloged, the procedure that uses the PassCharsmethod must be created, just as it must for a T-SQL-based procedure. The beginning of the command is the same as for a T-SQL procedure. The difference is that the end of the command has an EXTERNAL NAME instead of an implementation. The EXTERNAL NAME is used to refer to the assembly, namespace, class, and name of the method that implements the procedure. We have designed our procedure to have the same signature as PassCharsTSqldoes; the command that creates it follows. CREATE PROC PassChars(@str char(100)) as EXTERNAL NAME procedures.[Procedures.Parameters].PassChar The syntax of an EXTERNAL NAME is broken into three parts. The first part is the name of the assembly, which must have already been cataloged in the database. The second part is separated from the first by a . and is the class name, including the namespace if there is one. Note that when a namespace is used, the namespace and class must be enclosed in [] . If the class is not part of a namespace, the [] are not required. The third part is separated from the second by a . and is the name of the method that implements the procedure. Figure 3-2 shows the structure of an EXTERNAL NAME. Parameters in a method for a procedure are passed by value for an INPUT and by reference for an OUTPUT argument in a T-SQL stored procedure. If they are passed by value, changes to the parameter inside the procedure are not reflected back to the caller. If they are passed by reference, changes to the parameter are passed back to the caller. In a C# method, the ref keyword is used to indicate that a parameter is a passed by reference. In a VB.NET program, the ByRefkeyword is used. A value passed by reference can be used as a return value. A method may have more than one parameter that is passed by reference, so it is possible for a method to have multiple return values. The following method has one parameter passed Assembly Namespace Class Method GEO.[Math.Arith].invert External Name Figure 3-2: Format of EXTERNAL NAME

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

PROCEDURES So, in general, when you are writing

PROCEDURES So, in general, when you are writing methods to be used to implement procedures and functions, it is important to always check whether an instance of SqlType you are using is representing a null. The following code is a corrected version of the PassCharsmethod that does this. 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; } } } The return value of the method used to implement the procedure will be the return code for that procedure. The return type for the method may be void, SqlInt32, SqlInt16, Int32, or Int16. If void is used, the procedure will always have a return code of 0. Once you have written a CLR method for a procedure, you must compile it and catalog its assembly, as was shown in Chapter 2. The following script can be run on the command line to compile and catalog the assembly shown earlier, which we will assume is in a file named procedures.cs. It will add this to a local database named Chapter3. Note that sqlcmd is a utility included with SQL Server 2005 that allows a SQL expression to be executed from the command line. csc /target:library procedures.cs sqlcmd d Chapter3 Q CREATE ASSEMBLY procedures FROM %CD%procedures.dll with PERMISSION_SET = SAFE The sqlcmd line has been wrapped to fit onto the page. Also note that %CD% will be expanded by the command shell to the current directory, which is where the assembly produced by cscwill be.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES In order

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES In order to create a CLR method that can duplicate the functionality of this procedure, we must first decide what types should be used for the parameters of the method. This is done by using Table 3-1, shown earlier in this chapter. Look down the third column of this table until you find the SQL native type you want to use in the procedure when it is called from T-SQL. In this case, you would look for the nchar type. The SqlType you should use for this parameter is in the first column of the row in this case, SqlString. Now you have enough information to write a method that will be the equivalent of the T-SQL PassCharsTSqlprocedure. It follows. 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; } } } The PassChars method almost duplicates the functionality of the PassCharTSql procedure, but it actually contains a bug. The bug is that it is not testing to see if str is representing a null. In T-SQL, nulls are often transparently handled. The following example SQL batch using PassCharTSqlshows this. DECLARE @i INT EXEC @i = PassCharsTSql null PRINT @i GO 0 Notice that PassCharsTSqlhad a return code of 0. If a null were passed to the PassChars implementation shown earlier, the implementation would throw an exception and raise a T-SQL error, as follows. DECLARE @i INT EXEC @i = PassCharsTSql null .Net SqlClient Data Provider: Msg 6522, Level 16, State 1, Procedure PassChars, Line 0 A CLR error occurred during execution of PassChars …

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

PROCEDURES desirable to copy the behavior of a

PROCEDURES desirable to copy the behavior of a SQL Server type. A SqlType has a Value property, which has its value in terms of its corresponding CLR type. A SqlType can be cast to another SqlType and to a CLR type, and some CLR types can be cast to SqlTypes. So far we have covered the types typically used for parameters and return values in methods that will be used for procedures and functions. Now we will look at how to use a CLR method as a SQL Server procedure, function, or trigger. Procedures A SQL Server procedure has parameters and a return code. The parameters themselves may be passed by value or by reference. The return code from a procedure is, by convention, meant to indicate the error status that results when the procedure is executed. Typically, a return code of 0 indicates that the procedure executed with out any error. A nonzero result code is used to indicate what part of the procedure failed. A SQL Server procedure is created using the CREATE PROCEDURE command. Before we look at the all the details of implementing a SQL Server procedure with the CLR, let s first look at a procedure implemented using T-SQL and then implement an equivalent procedure using a method. Note that this example violates the convention of using a return code only for an error status just to make the example compact and show the construction of a stored procedure. Stored procedures typically manipulate a database by, for example, inserting something into a table. Chapter 4 will discuss how to write functions that manipulate the database; this example is just to illustrate the construction of a function that implements a stored procedure. CREATE PROCEDURE PassCharsTSql (@c nvarchar(100)) AS BEGIN RETURN LEN(@c) END When this procedure is run, the return code will indicate the number of characters in @c. A short SQL batch that shows this follows. DECLARE @i INT EXEC @i = PassCharsTSql bcd PRINT @i GO

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services