SQL Prompt

SQL Prompt

Misuse of the scalar user-defined function as a constant (PE017)

Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to return a global constant: Unless you need to use these as constants in computed columns or constraints, it is generally safer Read more

SQL Prompt

Customizing the SQL Prompt built-in snippets: a better ‘ata’ snippet

Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for parameters, and you can invoke them directly from an SSMS query pane. SQL Prompt also comes with many useful built-in snippets, but sometimes we Read more

SQL Prompt

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

SQL Prompt’s Code Analysis feature helps you discover code issues and hidden pitfalls during code development, as you type. It also provides tips for improving your code, and includes links to documentation that offers advice on the about each of the supported rules, so you can decide on the best course of action. This is Read more

SQL Prompt

How to reformat a database in one operation

Inherited a database from another team? Changed your team policy on the way that you format SQL? What’s to stop you formatting the code of an entire database nicely, when you’re developing it? It can be done, but the process can take longer than you expect. This article will demonstrate a simple 3-step approach to Read more

SQL Prompt

How to Test SQL Server Functions and Procedures using SQL Prompt

When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a batch that is attached to the CREATE or ALTER script. This isn’t just to prevent you from making Read more

SQL Prompt

How to use the SQL Prompt snippet placeholders for selecting and copying text

There are four SQL Prompt snippet placeholders that are all about selection and copying of text: $PASTE$ Inserts the contents of the clipboard at that position. $SELECTEDTEXT$ Inserts the selected text. $SELECTIONSTART$ Indicates where you want the start of the new selection of code after you have executed the snippet $SELECTIONEND$ Specifies the end of Read more

SQL Prompt

Finding code smells using SQL Prompt: the SET NOCOUNT problem (PE008 and PE009)

SQL Prompt implements two static code analysis rules to check code for potential misuse of the SET NOCOUNT command: PE008 – SET NOCOUNT OFF is used PE009 – No SET NOCOUNT ON before DML Whenever you execute a query, a short message is returned to the client with the number of rows that are affected Read more

SQL Prompt

Finding code smells using SQL Prompt: old-style join syntax (ST001)

SQL Prompt implements a static code analysis rule, ST001, which will check code automatically, during development and testing work, for occurrences of non-ANSI standard JOIN syntax. The “old style” Microsoft/Sybase JOIN style for SQL, which uses the =* and *= syntax, has been deprecated and is no longer used. Queries that use this syntax will Read more

SQL Prompt

Finding code smells using SQL Prompt: procedures that lack schema-qualification

SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE command, without the schema being specified. PE001 – Schema name for procedure is not specified Even where you don’t have to qualify the name of Read more

SQL Prompt

Why your Development team needs SQL Prompt

Alongside specialists, an IT department needs people who are broadly skilled and adaptable, who can pivot quickly to new projects, slot easily into new teams, and be productive quickly. SQL Prompt is the industry-leading SQL IntelliSense and code-formatting tool, and is an investment in developer versatility and productivity. It will both increase personal coding productivity Read more