Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Use SQL Complete for T-SQL Code

DZone's Guide to

How to Use SQL Complete for T-SQL Code

When looking for a tool to help me code faster and generate code automatically for a project with many stored procedures, I discovered SQL Complete. Here's how to use it.

· Database Zone ·
Free Resource

Download the Altoros NoSQL Performance Benchmark 2018. Compare top NoSQL solutions – Couchbase Server v5.5, MongoDB v3.6, and DataStax Enterprise v6 (Cassandra).

I was recently working on a project with several stored procedures, functions, and scripts in T-SQL, and I was wondering if there were tools that could help me code faster and generate code automatically.

After Googling and downloading some tools, I decided to check SQL Complete. The experience was fantastic — I could code faster, easily modify and refactor code, and check function definitions. And it generated a lot of code automatically. I reduced typo errors and synaxis errors and I could focus on the logic of the code instead of small format details that the tool could handle easily.

In this article, I will talk about this tool and some of the features offered.

Requirements

  • SQL Complete installed
  • SQL Server installed
  • SQL Server Management Studio

Getting Started

This tool is a plug-in in SSMS or Visual Studio. In this example, we test the tool in SSMS. We are assuming that you already installed the tool.

Create Stored Procedure

If we want to create a stored procedure in a T-SQL script, we right-click and write the letters "cre" and it will show the CREATE options. We will select the option CreateProcedure:

Image title

CreateProcedure will create the following code:

IF Object_id('dbo.usp_procedure_name') IS NOT NULL 
  SET noexec ON 
go 
CREATE PROCEDURE dbo.Usp_procedure_name 
AS 
    RETURN; 
go 
SET noexec OFF 
go 
ALTER PROCEDURE dbo.Usp_procedure_name () 
-- WITH ENCRYPTION, RECOMPILE, EXECUTE AS CALLER|SELF|OWNER| 'user_name' 
AS 
  BEGIN 
      SET nocount ON; 
      RETURN 0; 
  END 
go 

The first sentence will use an IF to verify if the object exists. If it exists, it can create the procedure. It also includes code to alter the procedure. Also, it created some comments about the use of functions like the encryption, recompile, and the EXECUTE AS, which are commonly used in stored procedures.

It is also possible to generate code for functions, triggers, indexes, tables and more.

If Clauses

You can easily create IF clauses using SQL Complete.

Let’s write the word "if" to generate code:

Image titleIt will ask you to press Tab. Let’s do it:

Image title

As you can see, it generates a BEGIN and END for the IF when you press Tab.

Working With Variables

Variables are used all the time in T-SQL. SQL Complete offers several features to deal with them.

In this new example, we will work with variables. Let’s create a variable:

DECLARE @country NVARCHAR(60)

When you try to use the variable, it is detected by the software. Type "@" and it will detect the existing variables:

Image title

When you click on the variable, it shows where the variable is used with a gray background. This is really useful when you need to modify code related to that variable and check which sections are affected:

Image title

Working With Functions

When you try to use a function, it provides help that includes an explanation, the data types used in the function, and the format for the input:

Image title

When the cursor is over a function, it will show information about the input, what the functions returns, and more:

Image title

This is very useful when we need to invoke a function and pass parameters. It is also useful to know what data will be returned by the function.

The following example shows the current date in the format MM/dd/YYYY unless the variable country is Italy. The code was created using SQL Complete:

DECLARE @country NVARCHAR(60)
SET @
IF @
BEGIN 
       SELECT FORMAT(GETDATE(),'dd/MM/yyyy')
END
ELSE
       SELECT FORMAT(GETDATE(),'MM/dd/yyyy')

Working Case Expressions

Case expressions can be used in T-SQL using SQL Complete. These are used to evaluate the value of an expression or condition and determine a result.

We are now going to write an example with the case statement write case and the following code will be generated. Write "case" and then press Tab:

   CASE input_expression
             WHEN when_expression1 THEN then_expression1
             WHEN when_expression2 THEN then_expression2
             -- ELSE
       END

Let’s run some code to complete this example:

SELECT  [ContactTypeID],
CASE Name
       WHEN 'Accounting Manager' THEN 'Account'
       WHEN 'Assistant Sales Agent' THEN 'Sales'
       WHEN 'Assistant Sales Representative' THEN 'Sales'
       WHEN 'International Marketing Manager' THEN 'Marketing'
        ELSE
'Other'
END
as Department
      ,[Name]
      ,[ModifiedDate]
  FROM [Adventureworks].[Person].[ContactType]

The code creates a new column named Department and groups each Person according to their position in a new Department.

Creating a New Table

If we want to create a new table in T-SQL, we only need to write the word "create" and select the option CreateTable:

Image title

The following code will be created automatically:

CREATE TABLE dbo.tbl_name
 (
       ID INT,
       CONSTRAINT PK_tbl_name_ID PRIMARY KEY (ID)
 )
 GO
CREATE TABLE dbo.tbl_name
 (
       ID INT,
       CONSTRAINT PK_tbl_name_ID PRIMARY KEY (ID)
 )
 GO
CREATE TABLE dbo.tbl_name
 (
       ID INT,
       CONSTRAINT PK_tbl_name_ID PRIMARY KEY (ID)
 )
 GO
CREATE TABLE dbo.customers
 (
       ID INT,
       cname VARCHAR (80),
       lastname VARCHAR (90),
       email VARCHAR (40)
       CONSTRAINT PK_customers_ID PRIMARY KEY (ID)
 )
 GO

If you write varchar in the code and it is lowercased, the tool will uppercase the words. The first time that you specify the table name, the primary constraint name will be changed according to the new table name.

Working With SELECT Statements

I really loved the option to generate SELECT statements to an existing table.

Let's run a simple SELECT to a table. When you write "se", it shows you the options that start with these two letters:

Image title

Once we write the SELECT statement, it can display the tables and columns. You only need to check the table and columns required:

Image title

As you can see, it generates the SELECT query automatically.

Working With Inner Joins

Inner joins, left joins, and outer joins are commonly used in T-SQL queries. This tool helps generate them easily.

In this new example, we will work in an inner join with T-SQL.

We will create first the SELECT and then the FROM:

SELECT edh.businessentityid, 
       edh.departmentid, 
       edh.shiftid 
FROM   humanresources.employeedepartmenthistory edh 

We will now add the inner join. Write "inn" and the software will suggest the inner join:

Image title

Select INNER JOIN and then you can specify the other table that you want to join the query. The query will create the ON statement automatically including the alias for both tables:

Image title

The SELECT statement generated will be the following:

SELECT edh.BusinessEntityID, edh.DepartmentID, edh.ShiftID, FROM HumanResources.EmployeeDepartmentHistory edh

INNER JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID

If you want to add columns in the query, it will detect them automatically when you write the alias name:

Image title

Get the Execution History

When you install SQL Complete, the SSMS includes an option in the menu named SQL Complete. Let’s test the Execution History:

Image title

The execution history shows the status of the execution (whether the query runs successfully, execution time, duration time, file, server, database, and the user who executed the query). It is also possible to create filters per date and to specify search words:

Image title

Rename Variables

The option in the menu Rename allows renaming functions or variables. Select the variable country in the following code:

DECLARE @country NVARCHAR(60)
SET @
IF @
BEGIN 
       SELECT FORMAT(GETDATE(),'dd/MM/yyyy')
END
ELSE
       SELECT FORMAT(GETDATE(),'MM/dd/yyyy')

Go to SQL Complete and select the option Rename (or press F2):

Image title

The option will show the variables and you will be able to modify the variable:

Image title

Format Queries

Now, we will give format to a query. Let’s write a simple query:

SELECT Name,GroupName FROM HumanResources.Department

In the next step, select the query, go to the SQL Complete menu, and select Format Selection:

Image title

The code will be changed to the following format:

SELECT
                    Name
                ,GroupName
             FROM HumanResources.Department

Drop and Alter Message Types

The features to drop and alter a message type are now available.

Let's create a message type:

CREATE MESSAGE TYPE 
[//dbforge.com/Expenses/SubmitExpense] 
VALIDATION = WELL_FORMED_XML ; 

Now write "DROP ME" and it will detect the word MESSAGE TYPE:

Image title

The following example shows the code to drop a message type:

DROP MESSAGE TYPE [//dbforge.com/Expenses/SubmitExpense] ;

The software also detects how to alter message types. Write "ALTER me" and the software will complete to ALTER MESSAGE TYPE:

Image title

It also detects the word VALIDATION in a message type:

Image title

And it also helps write the types of validation; in this example, EMPTY:

Image title

Work in SQLCMD Mode

The new version gives you suggestions in SQLCMD Mode. Go to Query and SQLCMD Mode:

Image title

Write ":" and it will show several reserved words for SQLCMD!:

Image title

The following example will create a database named dbforge using variables in SQLCMD mode:

:SETVAR DATABASENAME "dbforge"
CREATE DATABASE $(DATABASENAME);

Find Values in the Table

Another great feature is to find values in a table. It works like the "find" function in Excel, but you can now do it in the datagrid in SSMS. Write a simple query and in the results, right-click, and select Find:

Image title

In this example, it will look for the value 40. Note that it finds in all the columns the number 40! This is very useful when you need to search in several columns:

Image title

Jump Between Syntax Pairs

This option is useful when the code is long and you cannot find the END of the BEGIN or the BEGIN of the END. You just select the word BEGIN, for example, right-click, and select the option Jump Between Syntax Pairs in the Context menu:

Image title

The option will show the END of the BEGIN:

Image title

There are several features that are new like OLEDB provider suggestions, XML suggestions, jumps between column values, execution warnings, a CRUD generator, the ability to run a script on multiple databases, execution notifications, transaction reminders, the ability to copy result grid headers, and more.

Conclusion

As you can see, dbForge SQL Complete is a complete tool used to program in T-SQL. It helps you change the format and generate code.

I only showed a few options out of hundreds of features, but there are more. There is documentation that can be downloaded from the dbForge web site.

dbForge facilitates coding because it generates a lot of code automatically. It helps you follow T-SQL standards and helps you detect objects and create queries easily.

Learning this tool was natural and very easy. It took me just a few hours to learn most of the features and most of them are self-explanatory.

It can be used to complete statement code snippets and more — much more.

If you want to know more about this product, feel free to write your comments.

Download the whitepaper, Moving From Relational to NoSQL: How to Get Started. We’ll take you step by step through your first NoSQL project.

Topics:
t-sql ,database ,tutorial ,sql complete ,stored procedures

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}