Press "Enter" to skip to content

Category: Stored Procedures

Using Temporary Stored Procedures to Output Common Messages

Published 2025-10-30 by Kevin Feasel

Louis Davidson shows a neat use for temporary stored procedures:

On another connection (on another computer for that matter), I am right no doing some pretty long loads of some test data. The script is comprised of 6 queries, and they each may take 10 minutes (not completely sure, this is my first run of the scripts). And of course, I want to get some feedback on these queries to know how long they are taking.

One common way to do this is to put a PRINT statement between the queries so you can see the progress. But PRINT statements are notorious for one thing. Caching the output until the output buffer reaches some level.

One addition I’d make to Louis’s post is to make use of the FORMATMESSAGE() functionality that SQL Server 2016 introduced. This use case is right in its wheelhouse.

    SET @message = FORMATMESSAGE(N'%s%s%s%s',
			@Message,
			CASE
				WHEN @AddTimeToMessageFlag = 1 THEN CONCAT(N' : Message Time - ', SYSDATETIME())
				ELSE N''
			END,
			CASE
				WHEN @AddSpidToMessageFlag = 1 THEN CONCAT(N' : ProcessId - ', @@spid)
				ELSE N''
			END,
			CASE
				WHEN @AddOriginalLoginToOutputFlag = 1 THEN CONCAT(N' : LoggedInUserId - ', original_login())
				ELSE N''
			END);

FORMATMESSAGE() provides a moderate benefit to readability versus a lengthy CONCAT(). And if you always wanted to emit all fields versus the optional setup that Louis has in place, FORMATMESSAGE() makes the result even clearer to understand.

SET @message = FORMATMESSAGE(N'%s : Message Time - %s : ProcessId - %i : LoggedInuserId - %s',
			@Message,
			CAST(SYSDATETIME() AS NVARCHAR(100)),
			@@spid,
			original_login());
2 Comments

Temporary Stored Procedures in SQL Server

Published 2025-10-23 by Kevin Feasel

Louis Davidson is only here for a little while:

Pretty much every T-SQL programmer knows about temp tables very early in their journey with the language. They are generally wonderful tools that, when used properly, can be invaluable for storing data for a short amount of time. When used improperly they can be somewhat problematic, but certainly they are a tool we all use regularly.

But did you realize you can create a temporary stored procedure as well? It is not something I had ever used before, and while I remember hearing they existed a few times over the years, I had never tried them. The other day. I was creating an informal demo of some data, and once I had written the main query that listed rows that needed to be looked at, I needed a way to display the details of some rows.

I’ve worked with production code that included temporary stored procedures one time, I think. Otherwise, I’ve never used them either. But read on as Louis takes us through the utility of this concept.

Leave a Comment

Stored Procedures and Headers

Published 2025-08-01 by Kevin Feasel

Andy Brownsword lays out an argument:

Code is an ever moving target. Version control and documentation only go so far, if they even exist. Sometimes all you have is the code in front of you.

This is why I always start stored procedures with a header.

There was a time I strongly resisted this idea, but if you are diligent about keeping this up to date, it can be very useful for record-keeping, especially if your company has a tendency to switch source control systems and not keep the history between moves.

Comments closed

Digital Signatures on SQL Server Stored Procedures

Published 2025-03-17 by Kevin Feasel

Rick Dobson signs a procedure:

This tip introduces the basics of digital signatures based on self-signed certificates for selected T-SQL objects (stored procedures, functions, and triggers) with T-SQL samples. Learn how to detect what happens to a digital signature when a stored procedure is accidentally altered by a database administrator or intentionally modified by a bad actor.

Click through for the process.

Comments closed

Capturing Stored Procedure Executions via Extended Events

Published 2025-02-26 by Kevin Feasel

Haripriya Naidu creates an Extended Events session:

If you’re a DBA, how many times have developers asked you to check whether a particular stored procedure is hitting SQL Server?

Yes, we’ve all been there. Typically, you either run Profiler or set up an Extended Events session to trace it. But the question is, have you ever been confused about which event action to use?

Read on for the list of candidates and when you should use each.

I do mildly disagree with Haripriya’s last note around running Extended Events in production. There’s a small performance hit for most events. There are a few events that do have a much stronger impact, but for the large majority of events, you can (and probably should) have them running to provide the relevant diagnostic information. This isn’t like Profiler or even a server-side trace, where there’s a fairly significant overhead cost.

Comments closed

Natively Compiled Stored Procedures in SQL Server

Published 2025-01-29 by Kevin Feasel

Yvonne Vanslageren covers a point of frustration for me:

Modern applications often demand lightning-fast performance from their databases, whether they’re handling large transactional workloads or complex analytical queries. SQL Server’s in-memory OLTP feature addresses these needs by using memory-optimized tables and natively compiled stored procedures to boost throughput and reduce latency. This post provides an overview of natively compiled stored procedures, how to create them, and best practices for performance monitoring and maintenance.

My point of frustration is pretty simple: these things work really, really well. But they’re also so limited that I have never been able to use one in production. Memory-optimized tables are already so limited in good use cases, and natively compiled stored procedures have even more limitations, like using an awful collation (from the standpoint of humans working with the data) for string data.

Comments closed

Using Stored Procedures in Power BI

Published 2024-12-05 by Kevin Feasel

Annamarie Van Wyk makes a call:

The aim of this blog is to take you through one example of executing SQL stored procedures in Power BI. Similar to SQL Server Reporting Services (SSRS) we can create parameters that will be used to return a result set based on the output parameters that were selected.  The parameter selection will be available on the Power BI home page. The data used in his example is production line data. The production line is inside a factory where products are being packaged during a certain shift and at a certain production run date. It is literally a factory line with products being packed.

Read on for a simple answer, followed by how to parameterize the procedure call.

Comments closed