Get current stored procedure name?

Question

Inside my stored procedures, I always have a comment line to test my stored procedure:

/*
EXEC [schema].[uspNameOfSP]
*/

I want to simplify this a bit, so that I can copy paste it to other stored procedures without having to change the name everytime. I want to do something like this:

/*
EXEC @thisStoredProceduresName
*/

Is something like that possible?

Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Apr 05 '18 at 15:31
Wouldn't it be easier to just have all of your EXEC statements in a separate .sql file, and highlight the one you want and hit F5? — HardCode, Apr 05 '18 at 15:37
Possible duplicate of [Current executing procedure name](https://stackoverflow.com/questions/6034488/current-executing-procedure-name) — MatSnow, Apr 05 '18 at 15:40
@MatSnow that won't really work and I don't agree that it's a duplicate. The OP wants the name to automatically occur within the body of the stored procedure at rest, not output when the procedure is executed. — Aaron Bertrand, Apr 05 '18 at 15:44
If I understand what you mean - that you want that commented exec call placed into the body of every stored procedure - you could use a DDL trigger on the `CREATE_PROCEDURE` event, manipulate the body, and then execute an `ALTER` (and maybe build in logic that checks if the author of the stored procedure already put that comment in there manually, do avoid a double-stamp). But how is that line useful except in the case where a stored procedure takes *zero* parameters? — Aaron Bertrand, Apr 05 '18 at 15:46
@HardCode We're talking about many hundreds of stored procedures across more than 20 databases, in a team of about 10 people. It's easier for us to have the test line in the stored procedure itself than it is to maintain a sprawling list of all stored procedure names and their respective parameters, if they have any. — noClue, Apr 05 '18 at 15:48
@AaronBertrand Another problem with that solution: what if the stored procedure is renamed? — noClue, Apr 05 '18 at 15:49
@noClue so is your requirement changing, now you need the list of parameters, too? If you just need a list of all procedures and their parameters, you can easily derive this at any point by querying `sys.objects` and `sys.parameters`, and even building sample calls from this - this is all in the metadata, you don't have to manually document everything you create. — Aaron Bertrand, Apr 05 '18 at 15:50
@noClue renames can also be handled through the DDL trigger, but how often do you rename things? I find it's cleaner to drop the old one and create the new one. — Aaron Bertrand, Apr 05 '18 at 15:51
Aside: Hopefully your SP names don't really start with the prefix `sp`. That belongs to Microsoft. — HABO, Apr 05 '18 at 15:56
Ummm....isn't the name and all the parameters pretty visible? It is right there after "Create procedure". Oddly enough all the parameters are right after that. What benefit does having a comment in the body of the procedure provide? All the information is there. — Sean Lange, Apr 05 '18 at 15:58
@AaronBertrand Requirements didn't change, all I want is to use a variable of sorts to grab the name of the procedure, so I dont have to manually add the name of the procedure. Whether I need to have parameters afterwards is irrelevant. Obviously I would have to change the line slightly to include the test parameters in procedures that require parameters, but I'd still get the name of the procedure. — noClue, Apr 05 '18 at 15:58
@SeanLange To avoid annoying copy paste work. It's easier to paste one universal snippet of code in multiple procedures than it is to copy and modify a line in each procedure. I also wanted to make sure the test line is correct even if you rename the procedure. — noClue, Apr 05 '18 at 16:01
@HABO I think you meant `sp_`, which the OP didn't use. And it doesn't belong to Microsoft, there's just a minuscule performance hit if you use it. — Aaron Bertrand, Apr 05 '18 at 16:02
But to what benefit? You still have to copy and paste right? You would need to provide values for the parameters to test it. Seriously we are talking a few seconds of saved time at best. And what you are asking for is if it possible to have sql server create self documenting procedures. This type of thing is incredibly difficult at compile time. It isn't even easy at runtime. Think about reflection in dotnet. You are basically looking for compile time reflection. — Sean Lange, Apr 05 '18 at 16:04
@SeanLange Maybe I'm in the minority here (probably), but I find my method much easier to do and less error-prone. Yes, I'd still have to add the parameters manually, but at least I can start with a line of code that is universal across all procedures (if it existed). — noClue, Apr 05 '18 at 16:13
No I see where you are going with it. But you are looking for self documenting code. I just don't see this being possible. — Sean Lange, Apr 05 '18 at 16:14
@SeanLange I already feared it doesn't exist, I just wanted to make sure I didn't miss anything. Stackoverflow is just reassurance at this point. — noClue, Apr 05 '18 at 16:16

Brian · Accepted Answer · 2018-04-07T11:51:01.957

2

You can't.

The reason is when you execute it to test you are only selecting and executing that single line of code so it's not part of or in a stored procedure when it executes.

edited Apr 07 '18 at 11:51

answered Apr 05 '18 at 15:44

Brian

6,717
2
23
31

score 0 · Answer 2 · answered Apr 05 '18 at 16:42

I think this is a bad idea, but it sort of accomplishes what you're looking for.

USE some_db;
GO

CREATE PROCEDURE /*EXECUTE */ usp_Proc AS
BEGIN
  SELECT 1;
END

I was dubious, but I ran the CREATE statement, then scripted out the proc and the commented EXECUTE statement stuck.

Get current stored procedure name?

2 Answers2