CODEWITHSUNDEEP
sqlsql-serverstored-proceduresssms
Jon
Jon

Reputation: 1

Variable in Stored Procedure

When I execute one of my stored procedures manually, I have to populate several variables.

Most of the variables don't change each time it is run; is it possible to pre-populate the "Value" box so that it only needs to be changed when necessary?

I am reluctant to hard code in the script as there was a series of interlinked procedures which I need to keep dynamic

Upvotes: 0

Views: 407

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

I'm going to go out on a limb here and guess that you're talking about SQL Server, and that you're executing your procedure through SSMS, because of your description of the graphical interface. In the future, please tag your question with the specific database platform that the question pertains to, and try to be responsive to early comments. You'll get answers much, much faster. (If I'm wrong, just undo the tagging I added to your question.)

Although stored procedures can contain variables, what you're talking about here are parameters; values that are passed into the procedure from the calling code or application.

Parameters can be defined with default values in their declarations.

CREATE OR ALTER PROCEDURE dbo.SomeProc (
   @SomeBigIntegerValue bigint = 42
)
AS...

When default values exist, the parameter becomes optional for the caller. The procedure can now be called with or without explicit parameters. Either of these will run.

EXECUTE dbo.SomeProc;
EXECUTE dbo.SomeProc 
    @SomeBigIntegerValue = 37;

In the first instance, the procedure will use the default value, 42. In the second instance, it will use the parameter value, 37.

You'll note that I named the parameter in the call. That's a best practice, generally, to avoid confusion, but it also allows you to send the parameters in any order. If you don't name them, they will be interpreted in the order they're declared, so you run all manner of risks there.

If you choose to execute the procedure through the GUI, the default values won't be pre-populated, but you can see which parameters have defaults and which don't by expanding the Parameters tab under the procedure name in SSMS. I couldn't find an example with defaults, but it'll looks something like this:

SSMS Parameters tab expanded

If you want the procedure to use the default value, just tick the Pass Null Value check box.

(In case you're wondering, we have a truncate proc so that our ETL service accounts can have scaled back permissions without having to do fully-logged, row-by-row deletions...)

Upvotes: 1

Related Questions