Friday 27 April 2012

Introduction to Transact SQL User-Defined Functions


Introduction

SQL Server 2000 supports User-Defined Functions (UDFs) - one or more Transact-SQL statements that can be used to encapsulate code for reuse. User-defined functions cannot make a permanent changes to the data or modify database tables. UDF can change only local objects for this UDF, such as local cursors or variables. 


There are three types of UDF in SQL Server 2000:

  • Scalar functions
  • Inline table-valued functions
  • Multistatement table-valued functions
  •  
    Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types.
     
    Inline table-valued functions return the result set of a single SELECT statement.
     
    Multistatement table-valued functions return a table, that was built with many TRANSACT-SQL statements.
    User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.



    1 . Scalar Functions
     
    Scalar functions return a data type such as int, money, varchar, real, etc. They can be used anywhere a built-in SQL function is allowed. The syntax for a scalar function is the following:

    1. CREATE FUNCTION [owner_name.] function_name
    2. ( [{ @parameter_name scalar_parameter_type [ = default]} [,..n]])
    3. RETURNS scalar_return_type
    4. [WITH <function_option> >::={SCHEMABINDING | ENCRYPTION]
    5. [AS]
    6. BEGIN
    7. function_body
    8. RETURN scalar_expression
    9. END
    10. A simple scalar function to cube a number would look like this:
    11. CREATE FUNCTION dbo.Cube( @fNumber float)
    12. RETURNS float
    13. AS
    14. BEGIN
    15. RETURN(@fNumber * @fNumber * @fNumber)
    16. END
    17. Surprisingly, user-defined functions (UDFs) support recursion. Here is an
    18. SQL Server 2000 UDF using the standard factorial example:
    19. CREATE FUNCTION dbo.Factorial ( @iNumber int )
    20. RETURNS INT
    21. AS
    22. BEGIN
    23. DECLARE @i int
    24. IF @iNumber <= 1
    25. SET @i = 1
    26. ELSE
    27. SET @i = @iNumber * dbo.Factorial( @iNumber - 1 )
    28. RETURN (@i)
    29. END
    30.  
    31.  


    On Other Example 


    The following scalar function returns a maximum amount of books sold for a specified title. This function should be executed in pubs database. If the title has no sales, the UDF will return zero.
    /* scalar function to return max amount of books sold
    ** for the specified title_id
    */

    CREATE FUNCTION dbo.udf_max_copies_sold_for_title (@title_id CHAR(6))
    RETURNS INT
    AS
    BEGIN

    DECLARE @qty INT
    -- initialize the variable at 0:
    SELECT @qty = 0
    
    SELECT 
        @qty = MAX(qty) 
    FROM sales 
    WHERE
    title_id = @title_id
    
    /* If there are no books sold for title_id specified 
    ** then return 0:
    */
    RETURN ISNULL(@qty, 0)
    
    END
     
    Now we can execute this function as follows:
    SELECT dbo.udf_max_copies_sold_for_title ('bu2075')

    Results:
    ----------- 
    55
     
     
    2. In-Line Table Functions

    In-line table functions are functions that return the output of a single SELECT statement as a table data type. Since this type of function returns a table, the output can be used in joins of queries as if it was a standard table. The syntax for an in-line table function is as follows:

    1. CREATE FUNCTION [owner_name.] function_name
    2. ( [{ @parameter_name scalar_parameter_type [ = default]} [,..n]])
    3. RETURNS TABLE
    4. [WITH <function_option>::={SCHEMABINDING | ENCRYPTION}]
    5. RETURN [(] select_statement [)]
    6. An in-line function to return the authors from a particular state would
    7. look like this:
    8. CREATE FUNCTION dbo.AuthorsForState(@cState char(2) )
    9. RETURNS TABLE
    10. AS
    11. RETURN (SELECT * FROM Authors WHERE state = @cState)
    Other Example

    The following in-line function returns the product name, quantity ordered, and supplier for the top five best sellers in a particular year in the Northwind database:
     
    /* in-line function to return quantity, product name and supplier
    ** for the top 5 best-selling products within the specified year
    */
    
    CREATE FUNCTION dbo.udf_top_5_best_sellers (@year INT)
    RETURNS TABLE
    AS
    RETURN
    
    SELECT TOP 5 
         SUM(quantity) AS quantity_ordered, 
         a.ProductID, 
         ProductName, 
         CompanyName AS Supplier
    FROM [order details] a INNER JOIN products b
       ON a.productid = b.productid
       INNER JOIN suppliers c ON c.supplierid = b.supplierid
       INNER JOIN orders d ON d.orderid = a.orderid
          AND DATEPART(YEAR, OrderDate) = @year
    GROUP BY a.productid, productname, CompanyName
    ORDER BY 1 DESC

    We can execute this function as follows:
     
    SELECT * FROM dbo.udf_top_5_best_sellers (1998)

    Results:
    quantity_ordered
    ProductID
    ProductName
    Supplier
    659
    13
    Konbu
    Mayumi's
    546
    24
    Guaraná Fantástica
    Refrescos Americanas LTDA
    542
    60
    Camembert Pierrot
    Gai pturage
    513
    59
    Raclette Courdavault
    Gai pturage
    414
    7
    Uncle Bob's Organic Dried Pears
    Grandma Kelly's Homestead

    Alternatively, we can SELECT only the desired columns from this function:
     
    SELECT ProductID, 
            ProductName 
    FROM dbo.udf_top_5_best_sellers (1998)

    Results:
    ProductID
    ProductName
    13
    Konbu
    24
    Guaraná Fantástica
    60
    Camembert Pierrot
    59
    Raclette Courdavault
    7
    Uncle Bob's Organic Dried Pears





    3. Multistatement Table Functions
      Multistatement table functions are similar to stored procedures except that they return a table. This type of function is suited to address situations where more logic is required than can be expressed in a single query. The following is the syntax for a multistatement table function:
    1. CREATE FUNCTION [owner_name.] function_name
    2. ( [{ @parameter_name scalar_parameter_type [ = default]} [,..n]])
    3. RETURNS TABLE
    4. [WITH <function_option> >::={SCHEMABINDING | ENCRYPTION]
    5. [AS]
    6. BEGIN
    7. function_body
    8. RETURN
    9. END
    10.  
    Hierarchical data, such as an organizational structure, is an example of data that cannot be gathered in a single query. The Northwind Company database's Employees table contains a field called ReportsTo that contains the EmployeeID of the employee's manager. GetManagerReports is a multistatement table function that returns a list of the employees who report to a specific employee, either directly or indirectly.

    1. CREATE FUNCTION dbo.GetManagerReports ( @iEmployeeID int )
    2. RETURNS @ManagerReports TABLE
    3. (
    4. EmployeeID int,
    5. EmployeeFirstName nvarchar(10),
    6. EmployeeLastName nvarchar(20),
    7. Title nvarchar(30),
    8. TitleOfCourtesy nvarchar(25),
    9. Extension nvarchar(4),
    10. ManagerID int
    11. )
    12. AS
    13. BEGIN
    14. DECLARE
    15. @iRowsAdded int, -- Counts rows added to
    16. -- table with each iteration
    17. @PREPROCESSED tinyint, -- Constant
    18. for record prior
    19. -- to processing
    20. @PROCESSING tinyint, -- Constant
    21. for record
    22. -- being processed
    23. @POSTPROCESSED tinyint -- Constant for
    24. records that
    25. -- have been processed
    26. SET @PREPROCESSED = 0
    27. SET @PROCESSING = 1
    28. SET @POSTPROCESSED = 2
    29. DECLARE @tblReports TABLE (
    30. -- Holds employees added with each pass thru source employees table
    31. EmployeeID int,
    32. EmployeeFirstName nvarchar(10),
    33. EmployeeLastName nvarchar(20),
    34. Title nvarchar(30),
    35. TitleOfCourtesy nvarchar(25),
    36. Extension nvarchar(4),
    37. ManagerID int,
    38. ProcessedState tinyint
    39. DEFAULT 0
    40. )
    41. --Begin by adding employees who report to the Manager directly.
    42. INSERT INTO @tblReports
    43. SELECT EmployeeID, FirstName, LastName, Title, TitleOfCourtesy,
    44. Extension, ReportsTo, @PREPROCESSED
    45. FROM Employees
    46. WHERE ReportsTo = @iEmployeeID
    47. --Save number of direct reports
    48. SET @iRowsAdded = @@ROWCOUNT
    49. -- Loop through Employees table until no more iterations are necessary
    50. -- (e.g., no more rows added) to add all indirect reports.
    51. WHILE @iRowsAdded > 0
    52. BEGIN
    53. --Set just added employees ProcessedState to PROCESSING
    54. -- (for first pass)
    55. UPDATE @tblReports
    56. SET ProcessedState = @PROCESSING
    57. WHERE ProcessedState = @PREPROCESSED
    58. --Add employees who report to Managers in
    59. -- ProcessedState = PROCESSING
    60. INSERT INTO @tblReports
    61. SELECT e.EmployeeID, e.FirstName, e.LastName, e.Title,
    62. e.TitleOfCourtesy, e.Extension, e.ReportsTo, @PREPROCESSED
    63. FROM Employees e
    64. INNER JOIN @tblReports r ON e.ReportsTo = r.EmployeeID
    65. WHERE r.ProcessedState = @PROCESSING
    66. AND e.ReportsTo <> @iEmployeeID
    67. --Save number of rows added for this iteration
    68. SET @iRowsAdded = @@ROWCOUNT
    69. --Set ProcessedState to POSTPROCESSED for Managers whose
    70. --reports were added in this iteration
    71. UPDATE @tblReports
    72. SET ProcessedState = @POSTPROCESSED
    73. WHERE ProcessedState = @PROCESSING
    74. END
    75. --Save all data to output table
    76. INSERT INTO @ManagerReports
    77. SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Title,
    78. TitleOfCourtesy, Extension, ManagerID
    79. FROM @tblReports
    80. RETURN
    81. END
    82.  
    The output of this function would be used in the same manner as a standard table. Figure 1 demonstrates JOINING the output of GetManagerReports with the Employees table to produce a listing of the organizational structure of the Northwind Company:
    User function used in JOIN query
    Figure 1: User function used in JOIN query.



    Other Example


    The following multi-statement function accepts a delimited list as a parameter parses the string and returns the table containing each of the values in the parameter.
     
    /* multi-statement function to parse
    ** a delimited list and turn it into a rowset
    ** DEFAULT delimiter is comma
    */
    
    CREATE FUNCTION dbo.parse_comma_delimited_integer(
        @list    VARCHAR(8000), 
        @delimiter VARCHAR(10) = ',')
    
    -- table variable that will contain values
    RETURNS @tablevalues TABLE (
        item INT)
    AS
    BEGIN 
      DECLARE @item VARCHAR(255)
     
      /* Loop over the commadelimited list */
      WHILE (DATALENGTH(@list) > 0)
        BEGIN 
          IF CHARINDEX(@delimiter,@list) > 0
            BEGIN
              SELECT @item =
                 SUBSTRING(@list,1,(CHARINDEX(@delimiter, @list)-1))
              SELECT @list =
                    SUBSTRING(@list,(CHARINDEX(@delimiter, @list) +
                    DATALENGTH(@delimiter)),DATALENGTH(@list))
            END
    
         ELSE
            BEGIN
              SELECT @item = @list
              SELECT @list = NULL
            END
     
       -- Insert each item into temp table
        INSERT @tablevalues (
            item)
        SELECT item = CONVERT(INT, @item) 
       END
    
     RETURN
    END

    Now we can execute this function as follows:
    SELECT * FROM dbo.parse_comma_delimited_integer 
    ('39, 549, 324, 3556, 24, 2132, 345', ',')

    Results

    item
    39
    549
    324
    3556
    24
    2132
    345

    The following multi-statement UDF written for the Northwind database finds customers who have bought products in large quantities during a given year. The UDF accepts the year and amount of total sales and returns the customer name, the product that they have bought in large quantities, and all sales representatives who interfaced with these customers within the given year: 


    CREATE FUNCTION dbo.udf_top_customers_and_reps (
      @year INT, 
      @amount INT)
    
    RETURNS 
      @temp TABLE(
        ProductName VARCHAR(200), 
        CategoryName VARCHAR(200),
        CustomerID CHAR(5),
        CompanyName VARCHAR(200),
        TotalSales INT, 
        EmployeeNames VARCHAR(2000))
    
    AS 
    BEGIN
    
    /* populate the temp table with customers that have purchased any product 
    * with total sales greater than the specified amount, within the given year
    */
    
    INSERT @temp (
         ProductName ,
         CategoryName ,
         CompanyName ,
         CustomerID ,
         TotalSales 
    )
    
    
    SELECT 
        ProductName, 
        CategoryName, 
        CompanyName, 
        b.CustomerID, 
        SUM(a.UnitPrice * quantity) AS total_sales 
    FROM [order details] a 
    INNER JOIN orders b ON a.orderid = b.orderid
    INNER JOIN products c ON c.productid = a.productid
    INNER JOIN customers d ON d.customerid = b.customerid
    INNER JOIN categories e ON e.CategoryID = c.CategoryID
    WHERE DATEPART(YEAR, OrderDate) = @year
    GROUP BY c.ProductName, e.CategoryName, b.CustomerID, 
    d.CompanyName, DATEPART(YEAR, OrderDate) 
    HAVING SUM(a.UnitPrice * quantity) > @amount
    ORDER BY ProductName
    
    /* now get all the employees that have been involved with the customers
    * in the given year and return them in a comma-delimited list
    */
    
    
    DECLARE @CustomerID CHAR(5), 
        @EmployeeName VARCHAR(200)
    
    DECLARE @Employees TABLE (
     EmployeeName VARCHAR(80))
    
    DECLARE CustomerCursor CURSOR FOR
    SELECT CustomerID
    FROM @temp a 
    
    OPEN CustomerCursor
    FETCH NEXT FROM CustomerCursor INTO @CustomerID
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    INSERT @employees
    SELECT DISTINCT FirstName + ' ' + LastName 
     FROM Employees a INNER JOIN Orders b ON a.EmployeeID = b.EmployeeID
     AND DATEPART(YEAR,OrderDate) = @year
     WHERE b.CustomerID = @CustomerID
    
    
    /* create a comma-delimited list of employees */
    
    SELECT @EmployeeName = ''
     SELECT @EmployeeName = @EmployeeName + ', ' + EmployeeName
     FROM @Employees 
    
    SELECT @EmployeeName = SUBSTRING(@EmployeeName, 3, LEN(@EmployeeName)-2)
    
    
    
    UPDATE @temp
    SET EmployeeNames = @EmployeeName
    WHERE CustomerID = @CustomerID
    
    DELETE @Employees
    FETCH NEXT FROM CustomerCursor INTO @CustomerID
    END
    CLOSE CustomerCursor
    DEALLOCATE CustomerCursor
    
    
    RETURN
    END
    The following execution of the function returns customers who have bought any product in amounts of over $10,000 during 1997:
    SELECT * FROM dbo.udf_top_customers_and_reps (1997, 10000)
     
    Results



    ProductName
    CategoryName
    CustomerID
    Companyname
    TotalSales
    EmployeeNames
    Côte de Blaye
    Beverages
    MEREP
    Mère Paillarde
    10329
    Janet Leverling, Laura Callahan, Margaret Peacock, Michael Suyama, Nancy Davolio, Robert King
    Côte de Blaye
    Beverages
    SIMOB
    Simons bistro
    10540
    Andrew Fuller, Margaret Peacock, Robert King

     
    Invoking Functions


    There are a few syntax idiosyncrasies to observe when invoking user-defined functions. SQL Server 2000 provides some system-level user-defined functions in the Master database. These system functions are invoked with a slightly different syntax than ones that you would create. System functions that return a table have the following syntax:

    1.  
    2. ::function_name ([argument_expr], [,...])
    3. System functions that return a scalar value use this syntax:
    4. function_name ([argument_expr], [,...])
    5. User-created scalar and rowset functions are invoked in exactly the same
    6. manner. The syntax for invoking a user-created function looks like this:
    7. [database_name] owner_name. function_name ([argument_expr], [,...])
    8.  

    Limitations


     User-defined functions do have some restrictions placed upon them. Not every SQL statement or operation is valid within a function. The following lists enumerate the valid and invalid function operations: Valid:
    • Assignment statements
    • Control-flow statements
    • Variable declarations
    • SELECT statements that modify local variables
    • Cursor operations that fetch into local variables
    • INSERT, UPDATE, DELETE statement that act upon local table variables
    Invalid:
    • Built-in, nondeterministic functions such as GetDate()
    • Statements that update, insert, or delete tables or views
    • Cursor fetch operations that return data to the client

    Performance Implications

    Using UDFs will impact the performance of queries. The extent of the performance impact depends upon how and where you use a user-defined function. This is also true of built-in functions. However, UDFs have the potential for more dramatic performance hits than built-in functions. You should exercise caution when implementing functions in your queries and perform benchmarking tests to insure that the benefits of using your functions exceed the performance costs of using them.

    Uses for Functions


    Check constraints
     
    Scalar user-defined functions can be used as check constraints for columns in table definitions. As long as an argument to the function is a constant or built-in function or an argument is the column being checked, the function may be used to validate the column's value. These UDF check constraints provide the ability to use more complex logic for determining acceptable column values than Boolean expressions or LIKE patterns would allow.
    The following function validates that a serial number follows a specific pattern and portions of the serial number match a specific algorithm for a product type.

    
    CREATE FUNCTION dbo.ValidSerialNumber( @nvcSerialNumber nvarchar(50))
    RETURNS BIT
    AS
    BEGIN
    
    DECLARE
    @bValid  BIT,
     @iNumber  INT
    
     --default to invalid serial number
     SET @bValid = 0
    
     --Home Office Product
     IF @nvcSerialNumber LIKE
    '[0-9][A-Z][0-9][A-Z][0-9][0-9][0-9][0-9]'
     BEGIN
      SET @iNumber = CONVERT(int,RIGHT(@nvcSerialNumber,4))
      IF @iNumber % 7 = 2
      BEGIN
       SET @bValid = 1
      END
     END
    
     -- Video Game
     IF @nvcSerialNumber LIKE '[0-9][0-9][0-9][A-Z][0-9]5[A-Z]'
    
     BEGIN
      SET @iNumber = CONVERT(int,LEFT(@nvcSerialNumber, 3))
      IF @iNumber % 2 = 0
      BEGIN
       SET @bValid = 1
      END
     END
    
    RETURN ( @bValid)
    END
    
    CREATE TABLE dbo.CustomerProduct
    (
     CustomerID  int  NOT NULL PRIMARY KEY,
     ProductID  int  NOT NULL,
     SerialNumber  nvarchar(20) NOT NULL
     CHECK(dbo.ValidSerialNumber(SerialNumber) = 1)
    )
    
    
    Computed columns
     
    Scalar functions can be used to compute column values in table definitions. Arguments to computed column functions must be table columns, constants, or built-in functions. This example shows a table that uses a Volume function to compute the volume of a container:

    
    
    CREATE FUNCTION dbo.Volume (   @dHeight  decimal(5,2),
    @dLength  decimal(5,2),
    @dWidth decimal(5,2) )
    RETURNS decimal (15,4)
    AS
    BEGIN
     RETURN (@dHeight * @dLength * @dWidth )
    END
    
    
    CREATE TABLE dbo.Container
    (
     ContainerID  int  NOT NULL
     PRIMARY KEY,
     MaterialID  int  NOT NULL
    REFERENCES Material(MaterialID),
     ManufacturerID  int  NOT NULL
          REFERENCES
    Manufacturer(ManufacturerID)
     Height   decimal(5,2) NOT NULL,
     Length   decimal(5,2) NOT NULL,
     Width   decimal(5,2)  NOT NULL,
     Volume AS
      (
       dbo.Volume( Height, Length, Width )
      )
    )
    
    
    You should note that computed columns might be excluded from being indexed if user-defined functions determine their value. An index can be created on the computed column if the user-defined function is deterministic (e.g., always returns the same value given the same input). 

    Default constraints

    Default column values can be set with user-defined functions. UDFs can be very useful when a hard-coded value or built-in function does not suffice. For example, if a doctor's office wished to save a patient's appointment preference, a user-defined function could calculate the default day and time in a function by using the current date/time when the patient's record was created. If the patient's record were created on a Friday at 10:34 AM the AppointmentPref column would default to "Friday at 10:00" using the following function:

    
    CREATE FUNCTION dbo.AppointmentPreference ( @dtDefaultDateTime datetime )
    RETURNS nvarchar(50)
    AS
    BEGIN
    
    DECLARE @nDay   nvarchar(10),
     @nHour  nvarchar(6),
     @nPreference  nvarchar(50),
     @tiHour  tinyint
    
     --Get date description
     SET @nDay = DATENAME(dw, @dtDefaultDateTime )
    
     --Find current hour
     SET @tiHour = DATEPART(hh,@dtDefaultDateTime)
    
     --Use only 12-hour times
     IF @tiHour > 12
     BEGIN
      SET @tiHour = @tiHour - 12
     END
    
     --Don't allow appointments during lunch
     IF @tiHour = 12
     BEGIN
      SET @tiHour = 1
     END
    
     -- These are invalid hours
     IF @tiHour IN(5,6,7,8)
     BEGIN
      SET @tiHour = 4
     END
    
     --Create preference text
     SET @nPreference = RTRIM(@nDay) + '''s at ' +
    CONVERT(varchar(2),@tiHour) + ':00'
     RETURN ( @nPreference)
    END
    
    
    CREATE TABLE dbo.Patient
    (
     PatientID  int  NOT NULL PRIMARY KEY
          IDENTITY,
     FirstName  nvarchar(20) NOT NULL,
     LastName  nvarchar(20) NOT NULL,
     Addr1   nvarchar(50),
     Addr2   nvarchar(50),
     City   nvarchar(50),
     State   nvarchar(2),
     ZipCode  nvarchar(20),
     HomePhone  nvarchar(20),
     WorkPhone  nvarchar(20),
    AppointmentPref nvarchar(50)
    DEFAULT (dbo.AppointmentPreference(GETDATE()))
    
    )
    
    
    
    
    Assignments
     
    Scalar user-defined functions can be used to assign values to scalar variables. They may be used in any situation where a scalar built-in function may be used.

    
    
    DECLARE @fCube  float
    
    SET @fCube = dbo.Cube( 4.5 )
    
    
    
    Control flow
    Scalar user-defined functions may be used to control program flow when used in Boolean expressions.

    
    
    IF dbo.ValidSerialNumber('002A15A') = 1
     PRINT 'Yes'
    
    ELSE
     PRINT 'No'
    
    
    Case expressions
    User-defined functions that return a scalar value can be used in any of the cases of CASE expressions. The following example uses the DailySpecial function in a case function to determine what to display for a given day:

    
    
    CREATE FUNCTION dbo.DailySpecial( @nvcDay nvarchar(10))
    RETURNS NVARCHAR(100)
    AS
    BEGIN
    
    DECLARE @nvcSpecial nvarchar(100)
    
     SET @nvcDay = UPPER(@nvcDay)
    
     IF @nvcDay = 'SUNDAY'
      SET @nvcSpecial = 'Roast beef with green beans and
    baked potato'
    
     IF @nvcDay = 'MONDAY'
      SET @nvcSpecial = 'Chopped beef with green bean
    casserole'
    
     IF @nvcDay = 'TUESDAY'
      SET @nvcSpecial = 'Beef stew'
    
     IF @nvcDay = 'WEDNESDAY'
      SET @nvcSpecial = 'Beef pot pie'
    
     IF @nvcDay = 'THURSDAY' OR @nvcDay = 'FRIDAY'
      OR @nvcDay = 'SATURDAY'
      SET @nvcSpecial = 'Beef surprise'
    
    RETURN ( @nvcSpecial )
    END
    
    
    --Use output of DailySpecial function
    SELECT   Special =
        CASE DateName(dw, getdate())
    WHEN 'Sunday' THEN dbo.DailySpecial('Sunday')
             WHEN 'Monday' THEN
    dbo.DailySpecial('Monday')
              WHEN 'Tuesday' THEN
    dbo.DailySpecial('Tuesday')
     WHEN 'Wednesday' THEN dbo.DailySpecial('Wednesday')
             ELSE 'It's a mystery!'
    
          END
    
    
    Alternative to views
    Rowset functions, functions that return tables, can be used as alternatives to read-only views. Since views are limited to a single select statement, user-defined functions can provide greater functionality than a view. Powerful logic can be used when determining the records returned, which is not possible within a view. Also, views cannot accept parameters so a separate view must be created if the WHERE clause must change for different search c riteria.
    Alternative to temporary tables Rowset functions can be used as alternatives to temporary tables. For example, if you wished to find authors in the Pubs database who sold no books in a particular state, you could create a couple of functions that would generate the desired resultset.
    To find the quantity of books sold for a particular author in a given state you could write the following function:

    
    
    CREATE FUNCTION dbo.AuthorPoularityForState ( @cState Char(2))
    RETURNS  TABLE
    
    AS
    RETURN (
            SELECT a.au_id, a.au_fname, a.au_lname,
                   SUM(s.qty) AS QTY
            FROM Authors a
      INNER JOIN TitleAuthor ta ON a.au_id = ta.au_id
      INNER JOIN Titles t ON t.title_id = ta.title_id
      INNER JOIN Sales s ON s.title_id = t.title_id
      INNER JOIN Stores st ON st.Stor_ID = s.stor_id
     WHERE  st.state = @cState
     GROUP BY a.au_id, a.au_fname, a.au_lname
     ORDER BY QTY DESC, a.au_lname, a.au_fname
       )
    END
    
    
    You could then create another function that would use the output from the first function to find the authors in a particular state that have not had any sales:

    
    
    CREATE FUNCTION dbo.ReallyBoringAuthorsForState ( @cState Char(2) )
    RETURNS TABLE
    
    RETURN(
    SELECT a.au_id as AuthorID, a.au_fname AS AuthorFirstName,
     a.au_lname AS AuthorLastName, @cState AS State
     FROM AuthorPopularityForState(@cState ) pa
     RIGHT JOIN authors a ON pa.AuthorID = a.au_id
     WHERE IsNull(pa.UnitsSold, 0) = 0
    
    )
    
    
    The following SQL statement would list the California authors who had not sold any books:
    
    
    SELECT AuthorLastName, AuthorFirstName, AuthorID
     FROM ReallyBoringAuthorsForState('CA')
    ORDER BY AuthorLastName, AuthorFirstName
    
    
    List of authors without book sales
      List of authors without book sales

    Before the release of SQL Server 2000, temporary tables would likely have been used to generate the interim data to be used for the final query output. By using functions instead of temporary tables, potential table name concurrency problems are avoided. Functions also offer greater code reuse than temporary tables.



















    Tuesday 24 April 2012

    Progamiing C# Coding Standards | Project standered Code



    Anybody can write code. With a few months of programming experience, you can write 'working applications'. Making it work is easy, but doing it the right way requires more work, than just making it work.

    Believe it, majority of the programmers write 'working code', but not ‘good code'. Writing 'good code' is an art and you must learn and practice it.

    Everyone may have different definitions for the term ‘good code’. In my definition, the following are the characteristics of good code.

    ·         Reliable
    ·         Maintainable
    ·         Efficient

    Most of the developers are inclined towards writing code for higher performance, compromising reliability and maintainability. But considering the long term ROI (Return On Investment), efficiency and performance comes below reliability and maintainability. If your code is not reliable and maintainable, you (and your company) will be spending lot of time to identify issues, trying to understand code etc throughout the life of your application.


    To develop reliable and maintainable applications, you must follow coding standards and best practices.

    The naming conventions, coding standards and best practices described in this document are compiled from our own experience and by referring to various Microsoft and non Microsoft guidelines.

    There are several standards exists in the programming industry. None of them are wrong or bad and you may follow any of them. What is more important is, selecting one standard approach and ensuring that everyone is following it.


    If you have a team of different skills and tastes, you are going to have a tough time convincing everyone to follow the same standards. The best approach is to have a team meeting and developing your own standards document. You may use this document as a template to prepare your own document.

    Distribute a copy of this document (or your own coding standard document) well ahead of the coding standards meeting. All members should come to the meeting prepared to discuss pros and cons of the various points in the document. Make sure you have a manager present in the meeting to resolve conflicts.

    Discuss all points in the document. Everyone may have a different opinion about each point, but at the end of the discussion, all members must agree upon the standard you are going to follow. Prepare a new standards document with appropriate changes based on the suggestions from all of the team members. Print copies of it and post it in all workstations.

    After you start the development, you must schedule code review meetings to ensure that everyone is following the rules. 3 types of code reviews are recommended:

    1. Peer review – another team member review the code to ensure that the code follows the coding standards and meets requirements. This level of review can include some unit testing also. Every file in the project must go through this process.
    2. Architect review – the architect of the team must review the core modules of the project to ensure that they adhere to the design and there is no “big” mistakes that can affect the project in the long run.
    3. Group review – randomly select one or more files and conduct a group review once in a week. Distribute a printed copy of the files to all team members 30 minutes before the meeting. Let them read and come up with points for discussion. In the group review meeting, use a projector to display the file content in the screen. Go through every sections of the code and let every member give their suggestions on how could that piece of code can be written in a better way. (Don’t forget to appreciate the developer for the good work and also make sure he does not get offended by the “group attack”!)


    Note :
    The terms Pascal Casing and Camel Casing are used throughout this document.
    Pascal Casing - First character of all words are Upper Case and other characters are lower case.
    Example: BackColor
    Camel Casing - First character of all words, except the first word are Upper Case and other characters are lower case.
    Example: backColor

    1.     Use Pascal casing for Class names

    public class HelloWorld
    {
           ...
    }

    2.     Use Pascal casing for Method names

    void SayHello(string name)
    {
           ...
    }


    3.     Use Camel casing for variables and method parameters

    int totalCount = 0;
    void SayHello(string name)
    {
           string fullMessage = "Hello " + name;
           ...
    }

    4.     Use the prefix “I” with Camel Casing for interfaces ( Example: IEntity )

    5.     Do not use Hungarian notation to name variables.

    In earlier days most of the programmers liked it - having the data type as a prefix for the variable name and using m_ as prefix for member variables. Eg:

    string m_sName;
    int nAge;

    However, in .NET coding standards, this is not recommended. Usage of data type and m_ to represent member variables should not be used. All variables should use camel casing.

    Some programmers still prefer to use the prefix m_ to represent member variables, since there is no other easy way to identify a member variable.


    6.     Use Meaningful, descriptive words to name variables. Do not use abbreviations.

    Good:

    string address
    int salary

    Not Good:

    string nam
    string addr
    int sal

    7.     Do not use single character variable names like i, n, s etc. Use names like index, temp

    One exception in this case would be variables used for iterations in loops:

    for ( int i = 0; i < count; i++ )
    {
           ...
    }

    If the variable is used only as a counter for iteration and is not used anywhere else in the loop, many people still like to use a single char variable (i) instead of inventing a different suitable name.

    8.     Do not use underscores (_) for local variable names.

    9.     All member variables must be prefixed with underscore (_) so that they can be identified from other local variables.

    10.  Do not use variable names that resemble keywords.

    11.  Prefix boolean variables, properties and methods with “is” or similar prefixes.

    Ex: private bool _isFinished

    12.  Namespace names should follow the standard pattern

    <company name>.<product name>.<top level module>.<bottom level module>

    13.   Use appropriate prefix for the UI elements so that you can identify them from the rest of the variables.

    There are 2 different approaches recommended here.

    a.     Use a common prefix ( ui_ ) for all UI elements. This will help you group all of the UI elements together and easy to access all of them from the intellisense.

    b.     Use appropriate prefix for each of the ui element. A brief list is given below. Since .NET has given several controls, you may have to arrive at a complete list of standard prefixes for each of the controls (including third party controls) you are using.


    Control
    Prefix
    Label
    lbl
    TextBox
    txt
    DataGrid
    dtg
    Button
    btn
    ImageButton
    imb
    Hyperlink
    hlk
    DropDownList
    ddl
    ListBox
    lst
    DataList
    dtl
    Repeater
    rep
    Checkbox
    chk
    CheckBoxList
    cbl
    RadioButton
    rdo
    RadioButtonList
    rbl
    Image
    img
    Panel
    pnl
    PlaceHolder
    phd
    Table
    tbl
    Validators
    val



    14.  File name should match with class name.

    For example, for the class HelloWorld, the file name should be helloworld.cs (or, helloworld.vb)

    15.  Use Pascal Case for file names.




    1.     Use TAB for indentation. Do not use SPACES.  Define the Tab size as 4.

    2.     Comments should be in the same level as the code (use the same level of indentation).

    Good:

    // Format a message and display

    string fullMessage = "Hello " + name;
    DateTime currentTime = DateTime.Now;
    string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
    MessageBox.Show ( message );

    Not Good:

    // Format a message and display
    string fullMessage = "Hello " + name;
    DateTime currentTime = DateTime.Now;
    string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
    MessageBox.Show ( message );

    3.     Curly braces ( {} ) should be in the same level as the code outside the braces.

               
    4.     Use one blank line to separate logical groups of code.

    Good:
           bool SayHello ( string name )
           {
                  string fullMessage = "Hello " + name;
                  DateTime currentTime = DateTime.Now;

                  string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();

                  MessageBox.Show ( message );

                  if ( ... )
                  {
                         // Do something
                         // ...

                         return false;
                  }

                  return true;
           }

    Not Good:

           bool SayHello (string name)
           {
                  string fullMessage = "Hello " + name;
                  DateTime currentTime = DateTime.Now;
                  string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
                  MessageBox.Show ( message );
                  if ( ... )
                  {
                         // Do something
                         // ...
                         return false;
                  }
                  return true;
           }

    5.     There should be one and only one single blank line between each method inside the class.

    6.     The curly braces should be on a separate line and not in the same line as if, for etc.

    Good:
                  if ( ... )   
                  {
                         // Do something
                  }

    Not Good:

                  if ( ... )    {
                         // Do something
                  }

    7.     Use a single space before and after each operator and brackets.

    Good:
                  if ( showResult == true )
                  {
                         for ( int i = 0; i < 10; i++ )
                         {
                               //
                         }
                  }

    Not Good:

                  if(showResult==true)
                  {
                         for(int          i= 0;i<10;i++)
                         {
                               //
                         }
                  }


    8.     Use #region to group related pieces of code together. If you use proper grouping using #region, the page should like this when all definitions are collapsed.


    9.     Keep private member variables, properties and methods in the top of the file and public members in the bottom. 


    1.     Avoid writing very long methods. A method should typically have 1~25 lines of code. If a method has more than 25 lines of code, you must consider re factoring into separate methods.

    2.     Method name should tell what it does. Do not use mis-leading names. If the method name is obvious, there is no need of documentation explaining what the method does.

    Good:
           void SavePhoneNumber ( string phoneNumber )
           {
                  // Save the phone number.
           }

    Not Good:

           // This method will save the phone number.
           void SaveDetails ( string phoneNumber )
           {
                  // Save the phone number.
           }

    3.     A method should do only 'one job'. Do not combine more than one job in a single method, even if those jobs are very small.

    Good:
           // Save the address.
           SaveAddress (  address );
          
           // Send an email to the supervisor to inform that the address is updated.
           SendEmail ( address, email );           
          
           void SaveAddress ( string address )
           {
                  // Save the address.
                  // ...
           }
          
           void SendEmail ( string address, string email )
           {
                  // Send an email to inform the supervisor that the address is changed.
                  // ...
           }

    Not Good:

           // Save address and send an email to the supervisor to inform that
    // the address is updated.
           SaveAddress ( address, email );

           void SaveAddress ( string address, string email )
           {
                  // Job 1.
                  // Save the address.
                  // ...

                  // Job 2.
                  // Send an email to inform the supervisor that the address is changed.
                  // ...
           }

    4.     Use the c# or VB.NET specific types (aliases), rather than the types defined in System namespace.

           int age;   (not Int16)
           string name;  (not String)
           object contactInfo; (not Object)

                   
    Some developers prefer to use types in Common Type System than language specific aliases.

    5.     Always watch for unexpected values. For example, if you are using a parameter with 2 possible values, never assume that if one is not matching then the only possibility is the other value.

    Good:

    If ( memberType == eMemberTypes.Registered )
    {
           // Registered user… do something…
    }
    else if ( memberType == eMemberTypes.Guest )
    {
           // Guest user... do something…
    }
    else
    {
                  // Un expected user type. Throw an exception
                  throw new Exception (“Un expected value “ + memberType.ToString() + “’.”)

                  // If we introduce a new user type in future, we can easily find
    // the problem here.
    }

    Not Good:

    If ( memberType == eMemberTypes.Registered )
    {
                  // Registered user… do something…
    }
    else
    {
                  // Guest user... do something…

    // If we introduce another user type in future, this code will
    // fail and will not be noticed.
    }

    6.     Do not hardcode numbers. Use constants instead. Declare constant in the top of the file and use it in your code.

    However, using constants are also not recommended. You should use the constants in the config file or database so that you can change it later. Declare them as constants only if you are sure this value will never need to be changed.

    7.     Do not hardcode strings. Use resource files.

    8.     Convert strings to lowercase or upper case before comparing. This will ensure the string will match even if the string being compared has a different case.

    if ( name.ToLower() == “john” )
    {
               //…
    }

    9.     Use String.Empty instead of “”

    Good:

    If ( name == String.Empty )
    {
           // do something
    }

    Not Good:

    If ( name == “” )
    {
           // do something
    }


    10.  Avoid using member variables. Declare local variables wherever necessary and pass it to other methods instead of sharing a member variable between methods. If you share a member variable between methods, it will be difficult to track which method changed the value and when.

    11.  Use enum wherever required. Do not use numbers or strings to indicate discrete values.

    Good:
           enum MailType
           {
                  Html,
                  PlainText,
                  Attachment
           }

           void SendMail (string message, MailType mailType)
           {
                  switch ( mailType )
                  {
                         case MailType.Html:
                               // Do something
                               break;
                         case MailType.PlainText:
                               // Do something
                               break;
                         case MailType.Attachment:
                               // Do something
                               break;
                         default:
                               // Do something
                               break;
                  }
           }


    Not Good:

           void SendMail (string message, string mailType)
           {
                  switch ( mailType )
                  {
                         case "Html":
                               // Do something
                               break;
                         case "PlainText":
                               // Do something
                               break;
                         case "Attachment":
                               // Do something
                               break;
                         default:
                               // Do something
                               break;
                  }
           }
    12.  Do not make the member variables public or protected. Keep them private and expose public/protected Properties.

    13.  The event handler should not contain the code to perform the required action. Rather call another method from the event handler.

    14.  Do not programmatically click a button to execute the same action you have written in the button click event. Rather, call the same method which is called by the button click event handler.

    15.  Never hardcode a path or drive name in code. Get the application path programmatically and use relative path.

    16.  Never assume that your code will run from drive "C:". You may never know, some users may run it from network or from a "Z:".

    17.  In the application start up, do some kind of "self check" and ensure all required files and dependancies are available in the expected locations. Check for database connection in start up, if required. Give a friendly message to the user in case of any problems.

    18.  If the required configuration file is not found, application should be able to create one with default values.

    19.  If a wrong value found in the configuration file, application should throw an error or give a message and also should tell the user what are the correct values.

    20.  Error messages should help the user to solve the problem. Never give error messages like "Error in Application", "There is an error" etc. Instead give specific messages like "Failed to update database. Please make sure the login id and password are correct."

    21.  When displaying error messages, in addition to telling what is wrong, the message should also tell what should the user do to solve the problem. Instead of message like "Failed to update database.", suggest what should the user do: "Failed to update database. Please make sure the login id and password are correct."

    22.  Show short and friendly message to the user. But log the actual error with all possible information. This will help a lot in diagnosing problems.

    23.  Do not have more than one class in a single file.

    24.  Have your own templates for each of the file types in Visual Studio. You can include your company name, copy right information etc in the template. You can view or edit the Visual Studio file templates in the folder C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\ItemTemplatesCache\CSharp\1033. (This folder has the templates for C#, but you can easily find the corresponding folders or any other language)

    25.  Avoid having very large files. If a single file has more than 1000 lines of code, it is a good candidate for refactoring. Split them logically into two or more classes.

    26.   Avoid public methods and properties, unless they really need to be accessed from outside the class. Use “internal” if they are accessed only within the same assembly.

    27.   Avoid passing too many parameters to a method. If you have more than 4~5 parameters, it is a good candidate to define a class or structure.

    28.   If you have a method returning a collection, return an empty collection instead of null, if you have no data to return. For example, if you have a method returning an ArrayList, always return a valid ArrayList. If you have no items to return, then return a valid ArrayList with 0 items. This will make it easy for the calling application to just check for the “count” rather than doing an additional check for “null”.

    29.   Use the AssemblyInfo file to fill information like version number, description, company name, copyright notice etc.

    30.   Logically organize all your files within appropriate folders. Use 2 level folder hierarchies. You can have up to 10 folders in the root folder and each folder can have up to 5 sub folders. If you have too many folders than cannot be accommodated with the above mentioned 2 level hierarchy, you may need re factoring into multiple assemblies.

    16.  Make sure you have a good logging class which can be configured to log errors, warning or traces. If you configure to log errors, it should only log errors. But if you configure to log traces, it should record all (errors, warnings and trace). Your log class should be written such a way that in future you can change it easily to log to Windows Event Log, SQL Server, or Email to administrator or to a File etc without any change in any other part of the application. Use the log class extensively throughout the code to record errors, warning and even trace messages that can help you trouble shoot a problem.

    17.  If you are opening database connections, sockets, file stream etc, always close them in the finally block. This will ensure that even if an exception occurs after opening the connection, it will be safely closed in the finally block.

    18.  Declare variables as close as possible to where it is first used. Use one variable declaration per line.

    19.  Use StringBuilder class instead of String when you have to manipulate string objects in a loop. The String object works in weird way in .NET. Each time you append a string, it is actually discarding the old string object and recreating a new object, which is a relatively expensive operations.

    Consider the following example:

    public string ComposeMessage (string[] lines)
    {
       string message = String.Empty;

       for (int i = 0; i < lines.Length; i++)
       {
          message += lines [i];
       }

       return message;
    }

    In the above example, it may look like we are just appending to the string object ‘message’. But what is happening in reality is, the string object is discarded in each iteration and recreated and appending the line to it.

    If your loop has several iterations, then it is a good idea to use StringBuilder class instead of String object.

    See the example where the String object is replaced with StringBuilder.

    public string ComposeMessage (string[] lines)
    {
        StringBuilder message = new StringBuilder();

        for (int i = 0; i < lines.Length; i++)
        {
           message.Append( lines[i] );
        }

        return message.ToString();
    }



    1.     Always use multi layer (N-Tier) architecture.

    2.    Never access database from the UI pages. Always have a data layer class which performs all the database related tasks. This will help you support or migrate to another database back end easily.

    3.    Use try-catch in your data layer to catch all database exceptions. This exception handler should record all exceptions from the database. The details recorded should include the name of the command being executed, stored proc name, parameters, connection string used etc. After recording the exception, it could be re thrown so that another layer in the application can catch it and take appropriate action.

    4.    Separate your application into multiple assemblies. Group all independent utility classes into a separate class library. All your database related files can be in another class library.


    1.     Do not use session variables throughout the code. Use session variables only within the classes and expose methods to access the value stored in the session variables. A class can access the session using System.Web.HttpCOntext.Current.Session

    2.     Do not store large objects in session. Storing large objects in session may consume lot of server memory depending on the number of users.

    3.     Always use style sheet to control the look and feel of the pages. Never specify font name and font size in any of the pages. Use appropriate style class. This will help you to change the UI of your application easily in future. Also, if you like to support customizing the UI for each customer, it is just a matter of developing another style sheet for them


    Good and meaningful comments make code more maintainable. However,

    1.     Do not write comments for every line of code and every variable declared.

    2.     Use // or /// for comments. Avoid using /* … */

    3.     Write comments wherever required. But good readable code will require very less comments. If all variables and method names are meaningful, that would make the code very readable and will not need many comments.

    4.     Do not write comments if the code is easily understandable without comment. The drawback of having lot of comments is, if you change the code and forget to change the comment, it will lead to more confusion.

    5.     Fewer lines of comments will make the code more elegant. But if the code is not clean/readable and there are less comments, that is worse.

    6.     If you have to use some complex or weird logic for any reason, document it very well with sufficient comments.

    7.     If you initialize a numeric variable to a special number other than 0, -1 etc, document the reason for choosing that value.

    8.     The bottom line is, write clean, readable code such a way that it doesn't need any comments to understand.

    9.     Perform spelling check on comments and also make sure proper grammar and punctuation is used.


    1.     Never do a 'catch exception and do nothing'. If you hide an exception, you will never know if the exception happened or not. Lot of developers uses this handy method to ignore non significant errors. You should always try to avoid exceptions by checking all the error conditions programmatically. In any case, catching an exception and doing nothing is not allowed. In the worst case, you should log the exception and proceed.

    2.     In case of exceptions, give a friendly message to the user, but log the actual error with all possible details about the error, including the time it occurred, method and class name etc.

    3.     Always catch only the specific exception, not generic exception.

    Good:


           void ReadFromFile ( string fileName )
           {
                  try
                  {
                         // read from file.
                  }
                  catch (FileIOException ex)
                  {
                         // log error.
                         //  re-throw exception depending on your case.
                         throw;
                  }
           }

    Not Good:


    void ReadFromFile ( string fileName )
    {
       try
       {
          // read from file.
       }
       catch (Exception ex)    
       {
          // Catching general exception is bad... we will never know whether
          // it was a file error or some other error.            
          // Here you are hiding an exception.
          // In this case no one will ever know that an exception happened.

          return "";           
       }
    }
                   

    4.     No need to catch the general exception in all your methods. Leave it open and let the application crash. This will help you find most of the errors during development cycle. You can have an application level (thread level) error handler where you can handle all general exceptions. In case of an 'unexpected general error', this error handler should catch the exception and should log the error in addition to giving a friendly message to the user before closing the application, or allowing the user to 'ignore and proceed'.

    5.     When you re throw an exception, use the throw statement without specifying the original exception. This way, the original call stack is preserved.

    Good:

    catch
    {
           // do whatever you want to handle the exception

           throw;
    }

    Not Good:

    catch (Exception ex)
    {
           // do whatever you want to handle the exception

           throw ex;
    }

    6.     Do not write try-catch in all your methods. Use it only if there is a possibility that a specific exception may occur and it cannot be prevented by any other means. For example, if you want to insert a record if it does not already exists in database, you should try to select record using the key. Some developers try to insert a record without checking if it already exists. If an exception occurs, they will assume that the record already exists. This is strictly not allowed. You should always explicitly check for errors rather than waiting for exceptions to occur. On the other hand, you should always use exception handlers while you communicate with external systems like network, hardware devices etc. Such systems are subject to failure anytime and error checking is not usually reliable. In those cases, you should use exception handlers and try to recover from error.

    7.     Do not write very large try-catch blocks. If required, write separate try-catch for each task you perform and enclose only the specific piece of code inside the try-catch. This will help you find which piece of code generated the exception and you can give specific error message to the user.

    8.      Write your own custom exception classes if required in your application. Do not derive your custom exceptions from the base class SystemException. Instead, inherit from ApplicationException.