Press "Enter" to skip to content

Category: Stored Procedures

Finding Text in a Stored Procedure

Published 2021-05-17 by Kevin Feasel

Chad Callihan has a way to search the text of stored procedures:

Story time. Let’s say a database server is receiving a new release that include a change to a stored procedure. All of the databases are supposed to get the changes but one way or another there are problems with the release and it has to be stopped part of the way through. Maybe some changes got rolled back but others weren’t rolled back. We don’t have accurate logging of what databases have been updated but we want to know if a stored procedure is on the old version or received the new version.

I’ve used this technique quite often. The only downside is that if you have a lot of procedures and don’t specify the object ID, search can get a bit slow.

Comments closed

sp_QuickieStore in Action

Published 2021-04-28 by Kevin Feasel

Erik Darling has a new stored procedure. First up, an introduction:

If you’ve ever tried to write a query to find stuff in Query Store and quickly gotten frustrated, you’re not alone.

Whomever designed that schema hates you. It’s probably the same person who designed Extended Events.

I know, there’s a GUI. The stuff you can find in there is definitely useful, but I often find myself wishing it did a little bit more. Asking for additional features often feels like porridge-begging, and even if something new did get added eventually, it wouldn’t help you in the moment.

With all that in mind, I wrote sp_QuickieStore to try to take some of that pain away. As of this post, it’s V1. There will likely be changes and fixes and all that good stuff, but right now it’s good enough for an introduction.

Erik then shows off the results:

Under the more concise mode, you get one set of results back that combines metrics from query_store_runtime_stats, along with query text, query plan, and context settings. Some previews below. There’s a lot more in the actual results, but the screen caps would get very repetitive.

This looks really interesting, so go check it out.

Comments closed

Temp Tables and Nested Stored Procedures

Published 2021-03-29 by Kevin Feasel

John Morehouse takes us through a catch in creating temp tables inside nested stored procedures:

SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables.  One of the capabilities is to allow for stored procedures to call other stored procedures.  This is called “nesting” one stored procedure within the context of the calling stored procedure.  In addition, you can instantiate a temporary table from within the parent procedure that can be utilized within the context of the child procedure.

But wait! I’m not done yet!

You can also instantiate a temporary table with the same name as the parent temporary table within the child procedure.  But there is a catch!

I’ve done the former (and more frequently, had to support when somebody else did the former), but I don’t think I’ve done the latter. Mostly because it seems like it’s an invitation for problems.

Comments closed

Stored Procedure Return Values and Entity Framework Core

Published 2020-11-03 by Kevin Feasel

Erik Ejlskov Jensen shows us how to retrieve the return value from a stored procedure using Entity Framework Core:

SQL Server stored procedures can return data in three different ways: Via result sets, OUTPUT parameters and RETURN values – see the docs here.

I have previously blogged about getting result sets with FromSqlRaw here and here.

I have blogged about using OUTPUT parameters with FromSqlRaw here.

In this post, let’s have a look at using RETURN values.

Click through for the process.

Comments closed

With Query Store, Alter instead of Drop and Create

Published 2020-10-09 by Kevin Feasel

Erin Stellato has a recommendation when running Query Store:

When I talk about Plan Forcing I always discuss how users should ALTER procedures when using Query Store, and not use DROP and CREATE. This is valid beyond Plan Forcing cases; it’s a best practice I recommend however you are using Query Store. Every query stored in Query Store has an object_id associated with it, which ties it back to its object (stored procedure, function, etc.). This is critical not just for plan forcing, but also when you want to look at historical performance for a query after a change to the object.

Read on for a demonstration of why this is important.

Comments closed

Passing Power Query Parameters to Stored Procedures

Published 2020-08-28 by Kevin Feasel

Soheil Bakhshi shows how we can take an input from Power Query and pass it to a stored porcedure:

This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.

The solution is somewhat easy.

If you’re familiar with SQL Server Reporting Services, the solution instantly makes sense.

Comments closed

Generating Stored Procedure Mappings for Entity Framework Core

Published 2020-08-24 by Kevin Feasel

Erik Ejlskov Jensen takes us through stored procedure mapping with Entity Framework Core Power Tools:

In my previous post I showed how you can map and use stored procedures manually from EF Core, a process which involved quite a bit of code, and some changes to your derived DbContext.

With the latest release of EF Core Power Tools, you can opt-in to have any SQL Server stored procedures in your database made available to you.

Click through to learn how to do this.

Comments closed

Calling Stored Procedures with Output Parameters from Entity Framework Core

Published 2020-08-05 by Kevin Feasel

Erik Ejlskov Jensen shares how to call a stored procedure which includes an output parameter from Entity Framework Core:

In this post I will show how you can call stored procedures with OUTPUT parameters from EF Core. I am using the Northwind database for the sample code.

Erik’s code doesn’t have this problem, but using FromSqlRaw can lead to SQL injection problems if you aren’t careful with sanitizing inputs.

Comments closed

Against Encrypted Stored Procedures

Published 2020-06-23 by Kevin Feasel

Denny Cherry explains why encrypting stored procedures is a fool’s errand:

To this I point out, that if you’ve encrypted your code so that I won’t look at it by accident, you are actually getting the exact opposite result. Because you are encrypting code that means that I can’t see if. That means that I want to make sure that you aren’t hiding any stupid practices from me. That means that as soon as I see your encrypted procedures I’m decrypting them to see what is going on with this code.

Along with this, because you’ve bothered to encrypt the stored procedures that means that I can’t get an execution plan, and query store can’t be used for the queries within the stored procedure. And since I’m guessing that I can performance tune your database better then your developers can, I’m going to be decrypting the procedures so that I can tune the system.

They’re trivial to decrypt and Denny points out a few reasons why it’s just a bad idea.

Comments closed