Power User Blog

LINKS

General

ApexSQL Audit
ApexSQL Clean
ApexSQL Data Diff
ApexSQL Debug
ApexSQL Diff
ApexSQL Diff API
ApexSQL Doc
ApexSQL Edit
ApexSQL Enforce
ApexSQL Log
ApexSQL Log API
ApexSQL Recover
ApexSQL Refactor
ApexSQL Script
ApexSQL Source Ctl
ApexSQL SSIS Compare

Power User Blog Home

LINKS

Site Feed

MOST RECENT

Features in ApexSQL Edit that are not in MS SQL Server Management Studio

Improving the Readability of your Audit data

Expand * in SELECT statements to List all columns

Quickly convert SQL code to language-specific (e.g. C#, VB .Net) Client code

Quickly modify a SQL script so all object names are qualified

Quickly Convert your selected SQL code to a new stored procedure

Quickly Convert your selected SQL code to a new Inline Table-Valued Function

Quickly Convert your selected SQL code to a new Scalar Inline Function

Power User Blog

Articles about advanced features for advanced users

Features in ApexSQL Edit that are not in MS SQL Server Management Studio

Here's a WP discussing features that are in ApexSQL Edit but not in MS SQL Server's Management Studio. Click here to read.

AUTHOR
Dejan Apostolovic

Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info.

Labels: ApexSQL Edit

...

posted by Dejan Apostolovic on Thursday, December 31, 2009 0 comments

Improving the Readability of your Audit data

This article describes how to use two of advanced features of ApexSQL Audit - watches and lookups. These features help improve the readability of your audit data by:

a) Adding fields to your audit data that can be easily recognized by the users viewing your audit reports. For example, you can show the employee name of an audited row even if the employee name wasn't the actual column that was changed
b) Showing a more readable supporting value to an audited field. For example, if you're auditing a store id, showing "Ajax Health Store" (the store name) is better than showing 5568 (the store id itself).

What is a Watch?

A Watch is a field selected to be added to the audit log when another field in a table is changed (inserted, updated, deleted) even if the Watched field, itself, isn't. Multiple Watch fields can be set up for an audited table. Several different Watch fields can be added to a single audited field. And a single Watch field can be associated with multiple audited fields in the same column.

Why use Watches?

Watches allow you to add readily recognizable fields to any audit change which is helpful for people looking at reports. For example, no matter the change is in Sales you can see the Store Name for example, even if the Store itself was not changed. This makes audit reports more readable.

How to Create a Watch?

Watches are added at the special Watch panel. The Watch panel can be accessed from the View Menu and it is in the same panel group as Field Grid, Row Key, and Lookups. To access it from there, just select the Panel Tab for "Watches".

Example

To explain it better we'll use one example. Let assume that we have table Sales.SpecialOffer with the following structure:

Now, let assume that we want to audit changes in Discount Percents. This can be easily done by adding this column to audited columns.

Every change on this column will be included to report:

Now we can see that change was made on column DiscountPct and that old value was 10 and now it's 15. But we don't have additional information. We only have key - 2.

To add more information, we can use watch. So, every time when change happens, we want to beside these information have another one - description to make it more readable. This must be added every time when DiscoundPct is changed.

We should set Audit Field Name to DiscountPct and Watch Field Name to Description.

To make this working, we must recreate triggers.

Now, if DiscountPct is changed, we will see that Cup Sale, DVD Sale and Door Handle Sale discount is changed.

Lookup is another very useful possibility. This makes reports even more readable.

What is a Lookup?

Lookups are a powerful feature of ApexSQL Audit that allows you to show the Literal, from a related table, along with the Value of a field that was audited. This way a more human readable value can be seen in the audit reports. For example "Ajax Health Store" (the store name) is much better than 5568 (the store id).

Lookups are associated with the audited field so they will be triggered any time a row is inserted or deleted. They will be triggered if the specific column is changed in an update.

Using lookups, you can define an ad hoc relationship between columns from different tables. As with watch columns, if no audited columns are affected by the data change, then none of the lookup values are captured either.

Why use Lookups?

Lookups provide Human Readable Audit data for reporting purposes. For example, there may be a numeric ID field in a table that was changed but would be meaningless to someone viewing the audit report. By adding a Lookup though, the literal, from the related code table, can be shown in addition to the ID field.

How to Create a Lookup?

Lookups are added at the special Lookups panel. The Lookups panel can be accessed from the View Menu and it is in the same panel group as Field Grid, Row Key, and Watches. To access it from there, just select the Panel Tab for "Lookups".

Example

To explain it better, let's see another simple example.
Let assume that we have two tables Person.Person and Person.EmailAddress and they are connected via foreign key:

Now, let say we want to audit all newly added Email Addresses. This is easy to achieve. We only need to set insert trigger to Person.EmailAddress table.

If we add new email address, report will contain:

Also, we want to know real user name which is bound this email. We will achieve this by using lookups:

And, after recreating triggers, every new mail will be shown like this:

But, there is more space for improvements. You can combine field into custom expression, so if you want to see full name with First and Last name, you can do this as follows:

And report will contain full name like this:

Labels: ApexSQL Audit

...

posted by Milic Vuletic on Wednesday, December 09, 2009 0 comments

Expand * in SELECT statements to List all columns

The Expand Wildcards Refactor replaces all * wildcards in your SQL statements with its corresponding list of columns. Expanding wildcards allow you to make your code more resistant to breaking changes.

Here is an example of how this refactor expands the wildcard on this SQL statement:

Before Refactoring


SELECT *
FROM   apexsql.titles

After refactoring

SELECT titles.title_id,
       titles.title,
       titles.types,
       titles.pub_id,
       titles.price,
       titles.advance,
       titles.royalty,
       titles.ytd_sales,
       titles.notes,
       titles.pubdate
FROM   apexsql.titles

To use this feature in ApexSQL Edit, simply right-click the SQL code and select Refactor > Expand Wildcards from the context menu.

This feature is available in both ApexSQL Edit and ApexSQL Refactor 2010.

Labels: ApexSQL Edit, ApexSQL Refactor

...

posted by Janice Lee on Friday, December 04, 2009 0 comments

Quickly convert SQL code to language-specific (e.g. C#, VB .Net) Client code

The Copy SQL Code As refactor will add syntax for a selected language (e.g. C#, PHP, Ruby, C++) to your SQL code and copy it to the clipboard. You can customize how syntax will be added for each language as well as add new languages.

Below are some examples of how the refactor works.

If you had following SQL code:

CREATE TABLE [dbo].[ErrorLog] (
  [ErrorLogID]  [INT]    IDENTITY ( 1 , 1 )    NOT NULL,
  [ErrorTime]   [DATETIME]    NOT NULL,
  [UserName]    [SYSNAME]    COLLATE sql_latin1_general_cp1_ci_as    NOT NULL,
  [ErrorNumber] [INT]    NOT NULL)
GO
SELECT *
FROM   table errorlog

If you used Copy SQL Code As [name of language], your clipboard will hold properly transformed SQL code. Here are some examples on what will you get in your clipboard when using different languages.

Copy SQL Code As C#

string SQL = "CREATE TABLES [dbo].[ErrorLog]( \n"
           + "    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, \n"
           + "    [ErrorTime] [datetime] NOT NULL, \n"
           + "    [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, \n"
           + "    [ErrorNumber] [int] NOT NULL) \n"
           + "GO \n"
           + "SELECT * FROM TABLE ErrorLog";

Copy SQL Code As VB .NET

Dim SQL As String
SQL = "CREATE TABLES [dbo].[ErrorLog]( " + char(10) _
    + "    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, " + char(10) _
    + "    [ErrorTime] [datetime] NOT NULL, " + char(10) _
    + "    [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, " + char(10) _
    + "    [ErrorNumber] [int] NOT NULL) " + char(10) _
    + "GO " + char(10) _
    + "SELECT * FROM TABLE ErrorLog"

This options gives same results for previous versions of Visual Basic meaning you can use it for creating code for Visual Basic 6, too.

Copy SQL Code As PHP

<?php
string s$SQL = "CREATE TABLES [dbo].[ErrorLog]( \n"
     . "    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, \n"
     . "    [ErrorTime] [datetime] NOT NULL, \n"
     . "    [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, \n"
     . "    [ErrorNumber] [int] NOT NULL) \n"
     . "GO \n"
     . "SELECT * FROM TABLE ErrorLog";
?>

Copy SQL Code As Perl

$SQL = 'CREATE TABLES [dbo].[ErrorLog]( \n'
     . '    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, \n'
     . '    [ErrorTime] [datetime] NOT NULL, \n'
     . '    [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, \n'
     . '    [ErrorNumber] [int] NOT NULL) \n'
     . 'GO \n'
     . 'SELECT * FROM TABLE ErrorLog';

Copy SQL Code As Delphi

var
  SQL string;
begin
  SQL := 'CREATE TABLES [dbo].[ErrorLog]( ' + #10
       + '    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, ' + #10
       + '    [ErrorTime] [datetime] NOT NULL, ' + #10
       + '    [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ' + #10
       + '    [ErrorNumber] [int] NOT NULL) ' + #10
       + 'GO ' + #10
       + 'SELECT * FROM TABLE ErrorLog';
end;

Copy SQL Code As Ruby

SQL = 'CREATE TABLES [dbo].[ErrorLog]( \n'
   << '    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, \n'
   << '    [ErrorTime] [datetime] NOT NULL, \n'
   << '    [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, \n'
   << '    [ErrorNumber] [int] NOT NULL) \n'
   << 'GO \n'
   << 'SELECT * FROM TABLE ErrorLog'

Copy SQL Code As C++

std::string SQL;
SQL = "CREATE TABLES [dbo].[ErrorLog]( \n"
    + "    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, \n"
    + "    [ErrorTime] [datetime] NOT NULL, \n"
    + "    [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, \n"
    + "    [ErrorNumber] [int] NOT NULL) \n"
    + "GO \n"
    + "SELECT * FROM TABLE ErrorLog";

Language Customization

If you want to customizehow the SQL script is “copied” into the language of your choice, you can manually change each language’s settings. You will also be able to add and remove languages as well as modify existing ones. This can be done using the Customize Language Templates dialog, available from the context menu when you right click your code: Refactor > Copy SQL Code As > Customize Languages ...

This refactor comes with several predefined languages:
Java, Visual Basic, C#, C++, Ruby, PHP, Perl, Power Builder, Delphi, Dynamic SQL (T-SQL).

To use this refactor, simply select your code and right-click. From the Refactor context menu, select Copy SQL Code As > Language.

This refactor is available in ApexSQL Edit 2008.06 or higher and ApexSQL Refactor 2010.

Labels: ApexSQL Edit, ApexSQL Refactor

...

posted by Janice Lee on Thursday, December 03, 2009 0 comments

Quickly modify a SQL script so all object names are qualified

The Qualify Object Names refactor:

- Adds missing schema name to object names
- Adds missing datasource name (tables, views, etc) to column names

The schema name is added to the object name only if
- the object name doesn’t contain schema name yet,
- the object can have a schema (For example, Assembly can’t have a schema), and
- the object exists in the database

The datasource name is added to the column name in the following cases:
- Datasource name is not specified yet
- Datasource(table) has column with this name

Below is an example:

Before refactoring


SELECT DISTINCT
    ( ReportName ) ,
    DATEPART(hh , StatsDateNow) AS 'Hour' ,
    Day(StatsDateNow) AS 'Day' ,
    Month(StatsDateNow) AS 'Month' ,
    Year(StatsDateNow) AS 'Year'
FROM
    [MyReport] INNER JOIN [MyStats]
ON  StatsReportID = ReportID
ORDER BY
    ReportName ASC

After refactoring

SELECT DISTINCT
    ( MyReport.ReportName ) ,
    DATEPART(hh , MyStats.StatsDateNow) AS 'Hour' ,
    Day(MyStats.StatsDateNow) AS 'Day' ,
    Month(MyStats.StatsDateNow) AS 'Month' ,
    Year(MyStats.StatsDateNow) AS 'Year'
FROM
    dbo.[MyReport] INNER JOIN dbo.[MyStats]
ON  MyStats.StatsReportID = MyReport.ReportID
ORDER BY
    MyReport.ReportName ASC

To use this refactor, select your code and from the context menu, select Refactor > Qualify Object Name

This refactor is available in ApexSQL Edit 2008.05 and ApexSQL Refactor 2010.

Labels: ApexSQL Edit, ApexSQL Refactor

...

posted by Janice Lee on Thursday, December 03, 2009 0 comments

Quickly Convert your selected SQL code to a new stored procedure

This refactoring allows extracting code fragment into a separate stored procedure. New procedure is created encapsulating the selected code fragment. It doesn’t matter for this refactors what kind of query you plan to encapsulate: scalar one or returning data as stored procedures can contain both types. Seamlessly to Encapsulate as Scalar Function and Encapsulate as Table-Valued Function refactors this refactor will determine required parameters for the encapsulated query and define them as new procedure parameters.
Let’s review some examples of such refactoring.

Before refactoring


CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
       @BusinessEntityID [int] ,
       @JobTitle [nvarchar](50) ,
       @HireDate [datetime] ,
       @RateChangeDate [datetime] ,
       @Rate [money] ,
       @PayFrequency [tinyint] ,
       @CurrentFlag [flag]
       WITH EXECUTE AS CALLER
AS
BEGIN
      SET NOCOUNT ON ;
      BEGIN TRY
            BEGIN TRANSACTION ;
            UPDATE
                [HumanResources].[Employee]
            SET
                [JobTitle] = @JobTitle ,
                [HireDate] = @HireDate ,
                [CurrentFlag] = @CurrentFlag
            WHERE
                [BusinessEntityID] = @BusinessEntityID ;
            INSERT INTO
                [HumanResources].[EmployeePayHistory]
                (
                  [BusinessEntityID] ,
                  [RateChangeDate] ,
                  [Rate] ,
                  [PayFrequency] )
            VALUES
                (
                  @BusinessEntityID ,
                  @RateChangeDate ,
                  @Rate ,
                  @PayFrequency ) ;
            COMMIT TRANSACTION ;
      END TRY
      BEGIN CATCH
-- Rollback any active or uncommittable transactions before 
-- inserting information in the ErrorLog 
            IF @@TRANCOUNT > 0
               BEGIN
                     ROLLBACK TRANSACTION ;
               END
            EXECUTE [dbo].[uspLogError] ;
      END CATCH ;
END ;
GO

After refactoring the highlighted statement will be encapsulated into the separate stored procedure and the original code will be modified as follows:


CREATE PROCEDURE dbo.name
(
 @RateChangeDate [datetime] ,
 @HireDate [datetime] ,
 @BusinessEntityID [int] ,
 @JobTitle [nvarchar](50) ,
 @PayFrequency [tinyint] ,
 @CurrentFlag [flag] ,
 @Rate [money] )
AS
BEGIN
      UPDATE
          [HumanResources].[Employee]
      SET
          [JobTitle] = @JobTitle ,
          [HireDate] = @HireDate ,
          [CurrentFlag] = @CurrentFlag
      WHERE
          [BusinessEntityID] = @BusinessEntityID ;
      INSERT INTO
          [HumanResources].[EmployeePayHistory]
          (
            [BusinessEntityID] ,
            [RateChangeDate] ,
            [Rate] ,
            [PayFrequency] )
      VALUES
          (
            @BusinessEntityID ,
            @RateChangeDate ,
            @Rate ,
            @PayFrequency )
END
GO
-- Modified source: 
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
       @BusinessEntityID [int] ,
       @JobTitle [nvarchar](50) ,
       @HireDate [datetime] ,
       @RateChangeDate [datetime] ,
       @Rate [money] ,
       @PayFrequency [tinyint] ,
       @CurrentFlag [flag]
       WITH EXECUTE AS CALLER
AS
BEGIN
      SET NOCOUNT ON ;
      BEGIN TRY
            BEGIN TRANSACTION ;
            EXECUTE dbo.name @RateChangeDate , @HireDate ,
            @BusinessEntityID , @JobTitle , @PayFrequency , 
            @CurrentFlag , @Rate ;
            COMMIT TRANSACTION ;
      END TRY
      BEGIN CATCH
-- Rollback any active or uncommittable transactions before 
-- inserting information in the ErrorLog 
            IF @@TRANCOUNT > 0
               BEGIN
                     ROLLBACK TRANSACTION ;
               END
            EXECUTE [dbo].[uspLogError] ;
      END CATCH ;
END ;

Please note that the name of the created stored procedure (dbo.name in example) can be customized.

Context menu

Let’s review another example for Encapsulate as Stored Procedure refactor.

Before refactoring


DELETE  FROM
        [HumanResources].[Employee]
WHERE
        HireDate = @date ;
SELECT
    [HireDate] ,
    [VocationHours] ,
    [SickLeaveHours]
FROM
    [HumanResources].[Employee]
WHERE
    [VocationHours] = @Hours ;
INSERT INTO
    [HumanResources].[EmployeePayHistory]
    (
      [BusinessEntityID] ,
      [RateChangeDate] ,
      [Rate] ,
      [PayFrequency] 
VALUES
    (
      @BusinessEntityID ,
      @RateChangeDate ,
      @Rate ,
      @PayFrequency )

After refactoring


CREATE PROCEDURE dbo.name
(
 @RateChangeDate sql_variant ,
 @BusinessEntityID sql_variant ,
 @Hours sql_variant ,
 @PayFrequency sql_variant ,
 @Rate sql_variant ,
 @date sql_variant )
AS
BEGIN
      DELETE  FROM
              [HumanResources].[Employee]
      WHERE
              HireDate = @date ;
      SELECT
          [HireDate] ,
          [VocationHours] ,
          [SickLeaveHours]
      FROM
          [HumanResources].[Employee]
      WHERE
          [VocationHours] = @Hours ;
      INSERT INTO
          [HumanResources].[EmployeePayHistory]
          (
            [BusinessEntityID] ,
            [RateChangeDate] ,
            [Rate] ,
            [PayFrequency] )
      VALUES
          (
            @BusinessEntityID ,
            @RateChangeDate ,
            @Rate ,
            @PayFrequency )
END
GO
-- Modified source: 

EXECUTE dbo.name @RateChangeDate , @BusinessEntityID ,
@Hours , @PayFrequency , @Rate , @date

Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info.

Labels: ApexSQL Edit

...

posted by Dejan Batanjac on Thursday, September 24, 2009 0 comments

Quickly Convert your selected SQL code to a new Inline Table-Valued Function

Encapsulate As Inline Table-Valued Function refactor allows extracting a query fragment as inline table-valued function. This can be useful if same data query was used in multiple locations, so this allows just referring to a new function from the code.
Encapsulate As Inline Table-Valued Function refactor will recognize all variables and parameters used inside data query and transfer them into the result function parameters.
Before refactoring highlight SELECT statement:


CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, @city + ',' 
+ A.AddressLine1 + ',' + A.PostalCode AS UserAddress
 FROM Sales.Store AS S
     JOIN Sales.CAstomerAddress AS CA ON CA.CastomerID = S.CUstomerID
     JOIN Person.Address AS A ON A.AddressID = CA.AddressID
     JOIN Person.StateProvince SP ON SP.StateProvence = A.StateProvinceID
WHERE SP.Name = @name
GO

After refactoring the selected statement will be encapsulated into the table valued function and the original code will be modified as follows:


CREATE FUNCTION dbo.name
(
@city SQL_VARIANT,
@name SQL_VARIANT
)
RETURNS TABLE
AS
RETURN SELECT DISTINCT S.Name AS Store, @city + ',' 
+ A.AddressLine1 + ',' + A.PostalCode AS UserAddress
 FROM Sales.Store AS S
     JOIN Sales.CAstomerAddress AS CA ON CA.CastomerID = S.CUstomerID
     JOIN Person.Address AS A ON A.AddressID = CA.AddressID
     JOIN Person.StateProvince SP ON SP.StateProvence = A.StateProvinceID
WHERE SP.Name = @name
GO
-- Modified source:
CREATE VIEW CustomersByRegion
AS
    SELECT * FROM dbo.name(@city, @name)

Please note that the name of the created table valued function (dbo.name in example) can be customized.

Context menu

Let’s review another example of Encapsulate Inline Table – Valued Function refactor.

Before refactoring


CREATE PROCEDURE ImployeesInfo
    @LastName nvarchar (50),
    @FirstName nvarchar (50),
    @JoTitle nvarchar (50)
AS
    SELECT FirstName, LastName, vEmployee.Department + ': '
     + @JobTitle AS Position
    FROM HumanResources.vEmployeeDepartment
    WHERE vEmployee.FirstName = @FirstName AND vEmployee.LastName = @LastName
GO

After refactoring


CREATE FUNCTION dbo.name
(
@FirstName nvarchar (50),
@LastName nvarchar (50),
@JobTitle SQL_VARIANT
)
RETURNS TABLE
AS
RETURN SELECT  FirstName, LastName, vEmployee.Department + ': '
        + @JobTitle AS Position
       FROM HumanResources.vEmployeeDepartment
       WHERE vEmployee.FirstName = @FirstName AND
              vEmployee.LastName = @LastName
GO
-- Modified source: 
CREATE PROCEDURE ImployeesInfo
    @LastName nvarchar (50),
    @FirstName nvarchar (50),
    @JoTitle nvarchar (50)
AS
    SELECT * FROM dbo.name(@FirstName, @LastName, @JobTitle)

Labels: ApexSQL Edit

...

posted by Dejan Batanjac on Wednesday, September 23, 2009 0 comments

Quickly Convert your selected SQL code to a new Scalar Inline Function

For helping you to make your T-SQL code more readable we developed a new refactor that enables you to extract large expressions or a set of multiple expressions into a separate scalar function that can be used then from within different areas of your code.

Let’s review how Encapsulate as Scalar Inline Function refactor works on the below examples.

Before refactoring select a scalar expression to encapsulate:


declare @days int, @hours int, @holidays int, @vacation_period int
declare @rate money
declare @monthlysalary float, @totalsalary float, @vacationpayment float,
 @pensionFund float, @unemployment float, @insurance float

set @monthlysalary = @rate * @hours * @days
set @totalsalary = @monthlysalary - (@monthlysalary * @insurance /100) - (@monthlysalary * @pensionFund /100) * (@monthlysalary * @unemployment /100)
set @vacationpayment = @totalsalary * 12 / (365 - @holidays) * @vacation_period

After refactoring the selected expression will be encapsulated into the scalar function and the original code modified as follows:


CREATE FUNCTION dbo.TotalSalary
(
@monthlysalary float,
@unemployment float,
@insurance float,
@pensionFund float
)
RETURNS SQL_VARIANT
AS
BEGIN
    RETURN @monthlysalary - (@monthlysalary * @insurance /100) - (@monthlysalary * @pensionFund /100) * (@monthlysalary * @unemployment /100);
END
GO
-- Modified source:
declare @days int, @hours int, @holidays int, @vacation_period int
declare @rate money
declare @monthlysalary float, @totalsalary float, @vacationpayment float,
 @pensionFund float, @unemployment float, @insurance float

set @monthlysalary = @rate * @hours * @days
set @totalsalary = dbo.TotalSalary(@monthlysalary, @unemployment, @insurance, @pensionFund)

Please note that the name of the created scalar function (dbo.name in example) can be customized.

Context menu

Let’s review another example of Encapsulate as Scalar Inline Function refactor that shows how you can work with statements querying data.

Before refactoring


SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN @road THEN 'Road'
         WHEN @mountain THEN 'Mountain'
         WHEN @touring THEN 'Touring'
         WHEN @other THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;

After refactoring


CREATE FUNCTION dbo.name
(
@ProductLine SQL_VARIANT,
@road SQL_VARIANT,
@other SQL_VARIANT,
@touring SQL_VARIANT,
@mountain SQL_VARIANT
)
RETURNS SQL_VARIANT
AS
BEGIN
RETURN CASE @ProductLine
         WHEN @road THEN 'Road'
         WHEN @mountain THEN 'Mountain'
         WHEN @touring THEN 'Touring'
         WHEN @other THEN 'Other sale items'
         ELSE 'Not for sale'
      END;
END

-- Modified source:
SELECT ProductNumber, Category = dbo.name(ProductLine)

Note how all variables from the query are ported to the function variables. However you still need to customize the refactored code to make ProductLine field being a parameter as well. We plan to improve this refactor in the future version of ApexSQL Edit to catch up data fields automatically.

Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info.

Labels: ApexSQL Edit

...

posted by Dejan Batanjac on Friday, September 18, 2009 0 comments