Stored procedures

Frank Solomon
The solution result set.

Lever the TSQL MAX/MIN/IIF functions for Pinpoint Row Pivots

May 16, 2022 by

Introduction

For a recent Tsql development project involving text document records, the customer wanted a product that would show the latest and most recent earlier version of specific document text stored in a SQL Server 2014 database. One result set row had to hold all relevant information for each document: both document versions as described above, and relevant metadata. This essentially required fine-grained row pivots. This article will describe the engineering behind the solution, and compare that solution with the SQL Server PIVOT operator.

Read more »
Frank Solomon

Lever T-SQL for Pinpoint Control of ORDER BY in a Stored Procedure

June 1, 2020 by

Introduction

The T-SQL ORDER BY clause sorts SQL Server SELECT statement result sets, and it becomes important when we build stored procedures. Unfortunately, the syntax offers no flexible way to directly control the ORDER BY clause behavior with argument values. This means we don’t have an easy way to control the specific column or columns that the ORDER BY clause sorts. Additionally, SQL Server does not offer a flexible way to directly control the ascending or descending order of any ORDER BY clause column with argument values. Of course, we can certainly hard-code the ORDER BY clause in a stored procedure, but this approach becomes fixed in stone. We could try a dynamic SQL solution, involving a stored procedure code that dynamically builds and executes SQL Server statements inside a stored procedure. However, this technique becomes tricky, and it can lead to SQL injection attacks. Other techniques might rely on CASE statements, and their complexity can become overwhelming as the column count grows. This article spotlights a clean, efficient, pinpoint T-SQL stored procedure technique that directly sorts one, some, or all SELECT statement result set columns. The technique avoids dynamic SQL, and it operates directly in a stored procedure. The article also shows how to set the ascending or descending sort order of specific columns.

Read more »
Frank Solomon
Setting the target Directory

How to update the T-SQL Toolbox database

April 6, 2020 by

Introduction

In an earlier article, Solve Time Zone, GMT, and UTC problems using the T-SQL Toolbox database, I described T-SQL Toolbox, a free, open-source SQL Server database that handles time zone, date, and time calculations in a clean and efficient way. Available here at the CodePlex Archive, and here at GitLab, T-SQL Toolbox relies on time zone and time zone adjustment data in its two tables for many of its own calculations. However, T-SQL Toolbox does not update that data. I built primitive C-Sharp and VB.net applications that extract the latest time zone and time zone adjustment data from the Windows registry, and I discussed those applications in that earlier SQL Shack article. In the article, I explained that we can build SQL Server UPDATE statements with this extracted data, and then update the T-SQL Toolbox tables. This article describes a better approach and a better solution.

Read more »
Gerald Britton

Partial stored procedures in SQL Server

June 8, 2018 by

Introduction

SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility.

Read more »
Timothy Smith

Restricting and monitoring SQL Server data access with SQL views and stored procedures

May 17, 2018 by

This article explains data security for accessing sensitive data and restricts access in application using SQL Views and stored procedures.

We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users? Read more »

Daniel Calbimonte

How to email SQL query results to your smartphone using the sp_send_dbmail stored procedure

July 24, 2017 by

Introduction

Sometimes there are tasks that require too much time to finish during business hours and we need to leave the office with some still pending like the migration of data, backups or long running queries. In such cases, we would still want to receive an email in our smartphones to make sure that the job was completed successfully.

Read more »
Sifiso W. Ndlovu

How to calculate work days and hours in SQL Server

June 26, 2017 by

Like any other enterprise RDBMS system, SQL Server ships with several built-in functions that make developers’ T-SQL code clean, convenient and reusable. To demonstrate the efficiency of functions, say we needed to retrieve a server name for one of our SQL Server instances. Well, one of doing this would be to write a SELECT statement that would query the system view [sys].[servers] from the master database as shown in Script 1.

Read more »