SQL Server – SSMS Option ‘Execute Stored Procedure Shortcuts’ Explained

sql serverssmsstored-procedures

This is a wordy option; there is no documentation and web searches are no help. When I'm executing queries, I don't notice a difference with the option checked or not checked. What does this setting control?

Best Answer

This is certainly a wordy option and it seems a bit ambiguous to me. You could take "stored procedure shortcuts" a number of ways; initially, I did not reach the conclusion that these were actually referring to the "Query Shortcuts" panel in Tools > Options > Environment > Keyboard, mainly because I was asked a question about them when I wasn't staring at the dialog (the option is, of course, on that very panel).

The option was added in response to the following Connect item (which has been orphaned because Microsoft killed Connect due to GDPR):

Connect #266994 : Allow shortcuts to be configured with specific execution and results options

There, Simon Sabin pointed out that if you execute a stored procedure using a keyboard shortcut (e.g. Alt+F1 for sp_help, or Ctrl+1 for sp_who), that execution would obey any query options you had set for the underlying query window (e.g. SET STATISTICS TIME ON;). This isn't always desirable (the example Simon used was getting a bunch of graphical execution plans when hitting Alt+F1).

So:

If you expect these query shortcuts to execute with all of the same options as the current query window, leave the box unchecked.
If you expect these query shortcuts to ignore any existing query options, check the box (and open a new query window, as it doesn't affect current windows).

Unfortunately there isn't a way to customize this per shortcut, as Simon requested, so you'll have to take it all or nothing.

Related Solutions

MySQL Stored Procedure – Loop Through Table and Delete Rows

There are two possible solutions:

set @z to null in the very beginning of your loop (prior to SET @sql_text2 = concat('....)

or instead of

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

use this:

SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);

Explanation:

When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.
see: https://dba.stackexchange.com/a/35207/12923

Sql-server – Prevent a stored procedure from being executed outside SSMS

Instead of relying on shaky assumptions on the application name, why don't you just revoke permissions from those users that are not supposed to run the procedure?

You said that developers should not run it accidentally: revoke EXECUTE permissions from them and force them to use a different login to perform this task (if they are supposed to perform it, otherwise keep this privilege for you or the application administrator).

Best Answer

Related Solutions

MySQL Stored Procedure – Loop Through Table and Delete Rows

Sql-server – Prevent a stored procedure from being executed outside SSMS

Related Question