Solution Center

How to safely remove parameters from stored procedures or functions

2010-05-27T23:22:00.016-04:00

Problem:

Removing a parameter from a stored procedure or function is often necessary during normal database maintenance. However, safely removing a parameter requires updating all calls to the procedure or function, often making this a complex and time consuming task. Extra arguments in unrevised function and procedure calls cause argument count errors. Calls that don’t explicitly name arguments can pass arguments in the wrong order, cause data type errors, or improperly executed the function or procedure.

Unless all calls to the altered function or procedure are updated, these problems will proliferate and break the functionality of your database. Additionally, function or procedure calls that are executed infrequently can go unnoticed, throwing errors in the future that can often be hard to resolve.

You can spend a large amount of time hunting down these calls by hand, which is at best a tedious process, and at worst an impossible one. This is especially true if you are working on legacy databases created by other developers. Using SQL Server’s own dependency tracking system, you can find some of these dependencies, but this system is often incomplete, inaccurate, or both. You can read more about limitations of SQL Server’s dependency tracking system here, or on Microsoft’s own website.

Solution:

ApexSQL’s Remove Parameters refactor, (available in both ApexSQL Edit and ApexSQL Refactor), makes the process of removing parameters and updating dependent objects quick, simple, and accurate. Our Remove Parameters refactor takes advantage of ApexSQL’s proprietary parser, perfected with 3 years worth of development, to find and update all execution calls referencing the modified function or procedure. With a single process you can safely remove parameters without directly scripting.

Here’s how to take advantage of ApexSQL’s Remove Parameters refactor.

Step One:

The Remove Parameters refactor is available through the context (right-click) menu of every stored procedure or function in the Object Explorer pane, in both Edit and SSMS.

Right click the stored procedure or function that includes the parameter that you wish to remove, and select Refactors | Remove Parameters. This opens the Remove Parameters dialog box.

Step Two:

From the list of parameters, uncheck the parameter you wish to delete.

Click the Preview button to see the SQL script that will be used to remove the parameter and update all dependent function or procedure calls. When removing a parameter from a function, this script will also update the function signature. You can then click Refresh to update the script preview to reflect changes in parameters to be removed.

Step Three:

Click Remove Now to execute the generated script. You can also click the Open Script button to open the script in a new Query Editor window, in order to manually modify the script prior to running it.

The Remove Parameters refactor of ApexSQL Edit and ApexSQL Refactor is a powerful addition to any developer’s tool set. Combining the accuracy of our proprietary database parser and seamless integration with ApexSQL Edit or SSMS, ApexSQL’s Remove Parameter refactor makes SQL Development easier and more efficient. For information about ApexSQL’s full set of refactors, see ApexSQL Edit or ApexSQL Refactor.

Availability
Currently available in ApexSQL Edit 2010.10 and later
Not yet released. Will be available in ApexSQL Refactor 2010.03 and later

Easily find typos and simple scripting errors

2010-05-27T23:09:00.014-04:00

Problem

Small typos and simple scripting errors are a common source of frustration and lost efficiency for developers. A small error can occur early in the script and not be noticed until the script is executed, and fails. Finding small errors like this in large scripts, especially if there are multiple typos, can be a huge drain on time, since it requires going through the script line by line.

Solution

ApexSQL’s Syntax Error Highlighting feature, (available in both ApexSQL Edit and our SSMS add-in, ApexSQL Refactor), provides a quick and easy way to find syntax errors in real time. By underlining syntactic and semantic errors in red, similar to spell check, ApexSQL’s Syntax Error Highlighting helps prevent errors at runtime, and increases developer efficiency.

This feature is easily accessible from the query editor via the editor’s context menu. To activate, right click in your query editor and select Refactor | Check Syntax | Check Syntax (On/Off). You can also change the frequency that your script is checked for errors through this menu.

ApexSQL’s syntax checking engine underlines script errors in red, like below.

In addition, you can get a quick error description by holding your mouse over the underlined words.

ApexSQL’s engine currently check for more than 200 common errors, and our list is constantly growing. Here are just a few examples.

Variables are not allowed in the statement.
Query not allowed in WAITFOR.
Browse mode is invalid for a statement that assigns values to a variable.
The FOR UPDATE clause is invalid for statements containing set operators.
Batch/procedure exceeds maximum length of %d characters.
CREATE PROCEDURE contains no statements.
Case expressions may only be nested to level %d.
Correlation clause in a subquery not permitted.
Incorrect syntax for definition of the constraint.
Time value used with WAITFOR is not a valid value. Check date/time syntax.
Invalid money value.
The same large data placement option has been specified twice.
Incorrect syntax near the keyword.
Rule does not contain a variable.
Invalid expression in the TOP clause.
Cannot use SELECT INTO in browse mode.
Cannot use HOLDLOCK in browse mode.
The FOR BROWSE clause is no longer supported in views. Set the database compatibility level to 80 or lower for this statement to be allowed.
Cannot use the OUTPUT option when passing a constant to a stored procedure.
There are too many parameters in this statement. The maximum number is %d.
Cannot use the OUTPUT option in a DECLARE or CREATE FUNCTION statement.
Data stream is invalid for WRITETEXT statement in bulk form.
Data stream missing from WRITETEXT statement.
An invalid date or time was specified in the statement.
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
The object or column name starting with '%.*ls' is too long. The maximum length is %d characters.
Cannot use an existing function name to specify a stored procedure name.
Mixing old and new syntax to specify cursor options is not allowed.
Browse mode is invalid for subqueries and derived tables.
Cannot set or reset the 'parseonly' option within a procedure or function.
Function requires at least %d argument(s).
The integer value is out of range.
Cannot use If UPDATE within this CREATE TRIGGER statement.
The ON clause is not valid for this statement.

ApexSQL’s Syntax Error Highlighting, with its large and growing library of possible errors, provides a quick and easy way to prevent typos and scripting errors in real time, saving you time, and increasing your efficiency. For information about ApexSQL’s full set of refactors, see ApexSQL Edit or ApexSQL Refactor.

Availability
Currently available in ApexSQL Edit 2008.06 and later
Not yet released. Will be available in ApexSQL Refactor 2010.03 and later

Quickly convert SQL code to language-specific Client Code

2010-05-27T23:03:00.008-04:00

Problem

Integrating SQL Server with business infrastructure often requires developers to prepare T-SQL scripts for use within various client codes, (such as C#, PHP, and Perl). However, there isn’t an easy way to simply cut and paste T-SQL from a query editor and have the script refactored for the syntax of the destination language.

Solution

ApexSQL’s Copy SQL Code As refactor, (available in both ApexSQL Edit and our SSMS add-in, ApexSQL Refactor), provides a simple way to convert your script into the syntax of client languages. Simply cut and paste your script, and ApexSQL’s refactor will automatically translate the script to the appropriate syntax for the target language. In addition to the predefined languages, our Copy SQL Code As refactor allows you to create custom language templates, allowing support for any coding language.

Here’s how it works.

The Copy SQL Code As refactor is easily accessed through the context menu of the query editor. Simply select the script you wish to convert, right click, select Copy SQL Code As, and select your desired language.

Your script will now be refactored into the appropriate syntax, and placed on your clipboard, enabling you to easily paste it into the editor of your choice.

From this same menu, you are able to access the Customize Language Templates dialog, allowing you to make changes to existing language templates, or create completely new ones.

Below are some examples of scripts generated with our predefined templates.

Original T-SQL

CREATE TABLE [dbo].[ErrorLog] ( [ErrorLogID] [INT] IDENTITY( 1 , 1) NOT NULL,

[ErrorTime] [DATETIME] NOT NULL, [UserName] [SYSNAME] COLLATEsql_latin1_general_cp1_ci_as NOT NULL,

[ErrorNumber] [INT] NOT NULL)

SELECT *
FROM table errorlog

After Refactoring

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"

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";

ApexSQL’s Copy SQL Code As refactor provides a quick and easy way transfer T-SQL scripts into client code, saving you time, and increasing your efficiency. For information about ApexSQL’s full set of refactors, see ApexSQL Edit or ApexSQL Refactor.

Availability

Currently available in ApexSQL Edit 2008.06 and later

Currently available in ApexSQL Refactor 2010.01 and later

How to eliminate performance problems and prevent errors associated with “SELECT *”

2010-05-27T22:56:00.026-04:00

Problem:

Database development best practices suggest that implicitly referencing columns using SELECT * in queries should be avoided, due to potential problems associated with code readability, script robustness and query performance. SELECT * forces SQL Server to reconcile the referenced columns, creating an extra step in the query execution process that can result in as much as a 50% decrease in performance. In addition, wildcards can result in queries that return excessive data, returning unneeded columns in the result set. You can read more about this issue here, in an article from the MSDN library.

In addition to potential performance problems, SELECT * obscures the specific list of columns that have been referenced, which can result in unanticipated errors in calling code (i.e. client code) that are difficult to diagnose.

Explicitly referencing columns can avoid these performance issues, but implementing this practice by manually typing each column name is time consuming and error prone.

Solution:

ApexSQL's Expand Wildcards refactor, (available in both ApexSQL Refactor and ApexSQL Edit), takes advantage of the power of our proprietary parser to quickly and accurately replace the asterisk in SELECT * with the explicit column list, allowing developers to code both quickly and correctly.

This simple but powerful refactor can make significant increases developer efficiency and query performance by:

Making queries more transparent, increasing both individual and team efficiency
Eliminating a step from SQL Server’s execution process, when executing SELECT statements internally
Stabilizing queries against changes in referenced tables
Enabling developers to use only those columns specifically needed, preventing the query from taxing database resources in order to return unnecessary data

Tech Note: Propagating column name changes to all explicit column references is easy with ApexSQL’s Smart Rename refactor.

To make use of this refactor in ApexSQL Edit, right-click the SQL script in a query window (or use the hotkey, Ctrl+Shift+E), and select Refactor Expand | Wildcards.

To make use of this refactor via the ApexSQL Refactor add-in for SSMS, select Tools | ApexSQL Refactor | Expand Wildcards from the menu (or use the hotkey, Ctrl+Shift+E).

Here’s an example of a SELECT statement that has been refactored.

Before:		After:
`SELECT * FROM SalesLT.Address`		`SELECT Address.AddressID, Address.AddressLine1, Address.AddressLine2, Address.City, Address.StateProvince, Address.CountryRegion, Address.PostalCode, Address.rowguid, Address.ModifiedDate FROM SalesLT.Address`

The Expand Wildcards refactor of ApexSQL Edit and ApexSQL Refactor is an effective tool for preventing errors associated with asterisks in SELECT statements, improving quality and readability of scripts, improving query performance, and increasing developer efficiency. For more information about ApexSQL’s powerful refactors, see ApexSQL Edit or ApexSQL Refactor.

Availability
Currently available in ApexSQL Edit 2008.05 and later
Currently available in ApexSQL Refactor 2010.01 and later

How to implement risk free name changes for objects, columns, and parameters

2010-05-27T21:42:00.037-04:00

Problem:

Changing the name of an object, column, or parameter is a simple procedure, significantly complicated by the presence of referencing objects. A single name change can cause a cascade of broken dependencies, potentially compromising the structure and integrity of your entire database. Unless all dependent objects are updated, foreign key references to dependent columns will be broken, views and stored procedures that explicitly reference the old name will fail causing application breakage, and many other problems can be created.
SQL Server’s own dependency tracking systems (sysdepends, sp_msdependencies),are often incomplete, inaccurate, or both. You can read more about limitations of SQL Server’s dependency tracking system here, or on Microsoft’s own website. You can spend large amounts of time hunting down dependencies manually, which is at best a tedious process, and at worst an impossible one, if you are working on legacy databases created by other developers.

Solution:

ApexSQL has spent the equivalent of 3 years developing and testing our own proprietary parser that monitors dependencies in real time, quickly and accurately.Our Smart Rename refactor harness the power of this proprietary parser to easily propagate name changes throughout databases, consistently preventing broken dependencies and related errors. The Smart Rename refactor enables you to implement a name change without modifying a single line of script. This refactor is included in both ApexSQL Edit, and our SSMS add-in, ApexSQL Refactor.

Here’s how to take advantage of ApexSQL’s Smart Rename refactor.

Step One:

The Smart Rename refactor is available through the context (right-click) menu of each column.

Right click the column, parameter, or object that you wish to rename, and select Refactors | Smart Rename. This opens the Smart Rename dialog box.

Step Two:

Enter your desired new name. For items other than columns and parameters, you are also able to select a new schema.

Click the Preview button to see the SQL script that contains all SQL commands necessary to rename the selected object, column, or parameter and all dependent objects. You can then click Refresh to update the script preview if any changes have been made.

Step Three:

Click Rename Now to execute the generated script. You can also click Open Script to open the script in a new Query Editor window, in order to manually modify the script.

The Smart Rename refactor of ApexSQL Edit and ApexSQL Refactor is a powerful addition to any developer’s tool set. Combining the accuracy of our proprietary database parser and seamless integration into ApexSQL Edit or SSMS, ApexSQL’s Smart Rename refactor makes SQL Development easier and more efficient. For information about ApexSQL’s full set of refactors, see ApexSQL Edit or ApexSQL Refactor.

Availability
Not yet released. Will be available in ApexSQL Edit 2008.11 and later
Not yet released. Will be available in ApexSQL Refactor 2008.11 and later

Use ApexSQL products to manage your databases releases

2010-01-31T08:05:00.066-05:00

ApexSQL Diff and ApexSQL Edit allow you to manage your database versions releases.

A database release management automates the process of applying SQL code updated by developers to your production database. Use the links to several videos to review how this process can look like if you use ApexSQL Diff and ApexSQL Edit.

Step 1: How to create a initial project for your database in a Source Control system using ApexSQL Diff.

The step shows an architecture used in this example and explains how to export your current database structure to a Source Control system.
Click the link to review this step.

Step 2: How to create a developer's database based on the current state of the Source Control project using ApexSQL Diff.

The step shows how developers can synchronize their local, empty databases with a database structure stored in a Source Control system.
Click the link to review this step.

Step 3: How developers can change their local databases' structure using ApexSQL Edit.

The step shows how developers can add their local databases to the previously defined Source Control project, how to change definitions for database objects and saves changes in a Source Control system.
Click the link
to review the step.

Step 4: How to update a production database structure with a Source Control system using ApexSQL Diff.

The step explains how to propagate changes saved in your Source Control system project to your database by using ApexSQL Diff and its synchronization feature.
Click the link to review the step.

Find errors in your SQL code fast

2010-01-19T16:59:00.002-05:00

When you turn on the Syntax error highlighting option, our refactor engine will start checking your queries for any errors. Errors found will be marked by a red underline allowing you to find them quickly.

As you can see from above, all of the following errors are underlined:

1) the extra “OF” beside “SET QUOTED_IDENTIFIER”
2) TABLES is misspelled (should be “TABLE”)
3) The last select statement has an extra word “TABLE”

Here are some items that will be checked by this Syntax Error Highlighter refactor:
Note! There are more syntax checking rules then mentioned here.

Syntax Errors

- Unclosed quotation mark after the character string
select name as 'alias from sys.objects
- Missing end comment mark '*/'
select * from sys.objects/*

Semantic errors – SELECT Statement Errors

- The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
- ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
- The column prefix does not match with a table name or alias name used in the query.

SELECT table1.*
FROM   sys.objects table2

- The ORDER BY position number %ld is out of range of the number of items in the select list.

SELECT   *
FROM     sys.objects
ORDER BY -1,
         100

- A COMPUTE BY item was not found in the order by list. All expressions in the compute by list must also be present in the order by list.

SELECT   employeeid,
         orderdate,
         freight
FROM     northwind.dbo.orders
ORDER BY customerid,
         orderdate
COMPUTE SUM(freight) BY employeeid;

- ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

(All columns in ORDER BY should be present in select list if SELECT DISTINCT is used.)

SELECT   DISTINCT name
FROM     sys.objects
ORDER BY object_id

- A column has been specified more than once in the order by list. Columns in the order by list must be unique.

SELECT   *
FROM     sys.objects
ORDER BY object_id,
         2

SELECT   *
FROM     sys.objects
ORDER BY object_id,
         sys.objects.object_id

- The IDENTITY function can only be used when the SELECT statement has an INTO clause.

select identity(int,1,1) as id, * from sys.objects

- ORDER BY must not be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.

select object_id from sys.objects order by object_id
union
select object_id from sys.objects
order by object_id

- The correlation name '%.*ls' is specified multiple times in a FROM clause.
(The same aliases can’t be used inside single SELECT statement)

SELECT *
FROM   (SELECT object_id,
               name
        FROM   sys.objects) AS orders(customerid,nbr)
       JOIN (SELECT object_id,
                    name
             FROM   sys.objects) AS orders(customerid,nbr)
         ON orders.customerid = orders.customerid

Other semantic errors

- There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number

INSERT INTO t
           (id,
            name)
VALUES     (1,
            'name',
            'comment')

- A TOP N value may not be negative.

SELECT TOP ( -10 ) *
FROM   northwind.dbo.orders

- Subqueries are not allowed in this context. Only scalar expressions are allowed.
(Sub-queries are not allowed for some statements)

CREATE TABLE #t (
  id CHAR(10)    NULL,
  c1 TEXT CONSTRAINT df_working DEFAULT (SELECT *
   FROM   sys.objects))

- A GOTO statement references the label, but the label has not been declared.

GOTO label2
LABEL1:

- Variable should be declared before usage.

SET @a = 1

- Cannot use a BREAK statement outside the scope of a WHILE statement.

CREATE PROCEDURE P1(
                @id INT)
AS
  BREAK

  RETURN 2

- Cannot use a CONTINUE statement outside the scope of a WHILE statement.

CREATE PROCEDURE P1(
                @id INT)
AS
  CONTINUE

  RETURN 2

- Rule does not contain a variable.

CREATE RULE R1 AS 2>3;

- The definition for column '%.*ls' must include a data type.

CREATE TABLE t (
  c1 )

- Percent values must be between 0 and 100.

SELECT   TOP 150 PERCENT *
FROM     northwind.dbo.orders
ORDER BY orderid

- The number of rows in the TOP clause must be an integer.

SELECT TOP ( 0.25 ) *
FROM   sys.objects

DECLARE  @p  AS FLOAT;

SELECT @p = 10

SELECT TOP ( @p ) *
FROM   sys.objects

Other Errors

Other Errors that will be checked, but don’t have example (this list is not complete).

1. Query not allowed in WAITFOR.
2. Variables are not allowed in the statement.
3. Browse mode is invalid for a statement that assigns values to a variable.
4. The FOR UPDATE clause is invalid for statements containing set operators.
5. Batch/procedure exceeds maximum length of %d characters.
6. CREATE PROCEDURE contains no statements.
7. Case expressions may only be nested to level %d.
8. Correlation clause in a subquery not permitted.
9. Incorrect syntax for definition of the constraint.
10. Time value used with WAITFOR is not a valid value. Check date/time syntax.
11. Invalid money value.
12. The same large data placement option has been specified twice.
13. Incorrect syntax near the keyword.
14. Rule does not contain a variable.
15. Invalid expression in the TOP clause.
16. Cannot use SELECT INTO in browse mode.
17. Cannot use HOLDLOCK in browse mode.
18. The FOR BROWSE clause is no longer supported in views. Set the database compatibility level to 80 or lower for this statement to be allowed.
19. Cannot use the OUTPUT option when passing a constant to a stored procedure.
20. There are too many parameters in this statement. The maximum number is %d.
21. Cannot use the OUTPUT option in a DECLARE or CREATE FUNCTION statement.
22. Data stream is invalid for WRITETEXT statement in bulk form.
23. Data stream missing from WRITETEXT statement.
24. An invalid date or time was specified in the statement.
25. Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
26. The object or column name starting with '%.*ls' is too long. The maximum length is %d characters.
27. Cannot use an existing function name to specify a stored procedure name.
28. Mixing old and new syntax to specify cursor options is not allowed.
29. Browse mode is invalid for subqueries and derived tables.
30. Cannot set or reset the 'parseonly' option within a procedure or function.
31. Function requires at least %d argument(s).
32. The integer value is out of range.
33. Cannot use If UPDATE within this CREATE TRIGGER statement.
34. The ON clause is not valid for this statement.

Syntax Highlighting in ApexSQL Edit

It can be turned on/off by using context menu command on query editor.

While turned on, this refactor will perform syntax checks on specified check interval. When something wrong is found you can see additional error description by hovering your mouse over underlined words. You will get text tooltip that looks like this:

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.

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

2010-01-19T16:58:00.015-05:00

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 floatset @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.

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.

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

2010-01-19T16:56:00.002-05:00

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.

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)

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.

Quickly Convert your selected SQL code to a new stored procedure

2010-01-19T16:54:00.002-05:00

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.

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

Quickly modify a SQL script so all object names are qualified

2010-01-19T16:51:00.001-05:00

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.

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

2010-01-19T16:50:00.002-05:00

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.

Expand * in SELECT statements to List all columns

2010-01-19T16:48:00.003-05:00

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.

Improving the Readability of your Audit data

2010-01-19T16:39:00.019-05:00

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:

Welcome to Solution Center blog

2009-11-06T04:37:00.004-05:00

Welcome to the ApexSQL Solution Center Blog!

On this blog you will find answers to common problems that SQL DBAs and developers face, along with ways to use ApexSQL tools to solve these problems quickly and easily.

Here are a few articles to get you started:

How to eliminate performance problems and prevent errors associated with “SELECT *”

Quickly convert you selected SQL code to a new stored procedure

Improving the readability of you audit data

How to safely remove parameters from stored procedures or functions.

Quickly modify a SQL script so all object names are qualified

Use ApexSQL products to manage you database releases

How to implement risk free name changes for objects, column, and parameters