question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked Cathyji-msft answered

making a stored procedure interactive

is there a way for a stored procedure to move on to the next statement or maybe loop iteration only after person running it hits a return button or maybe types GO and then return? I think i've seen something like this before but that solution might only have allowed one interaction. the proc will update some data between a before select and after select ad if the operator doesnt like something he/she sees, there would be an option to stop.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered

No. T-SQL does not support user interaction at all. One way to do this would be to write a script (outside SQL) that executes some SQL, prompts the user and then executes some more SQL. Of course the feasibility of this is highly dependent upon what you're trying to do. It might also be possible to do it via SSIS task that the user runs but I've never looked to see if SSIS has any interactive components.

Having said that I will point out that, if enabled on the server, then you can call out to the shell using something like xp_cmdshell. However this is just screaming of insecure and therefore not on by default AFAIK and no reasonable DBA would enable it anyway. However if they did then in theory you could trigger a prompt by shelling out to wscript. But honestly that wouldn't make sense to me. Furthermore the SQL Server instance is probably running on a remote server as a service so I'm not even sure a shell script would work as the UI would pop up on a screen the user doesn't have access to. SSMS/Azure Data Studio and probably any other query execution program would not get notified of any UI.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Consider the debugging features of Visual Studio or the version 17.9.1 of Management Studio: https://docs.microsoft.com/en-us/answers/questions/191819/how-to-debug-sp-procedure-from-ssms.html.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @db042190-2983,

is there a way for a stored procedure to move on to the next statement

As Viorel mentioned, you can try to use the debugging feature in SSMS. Please refer to the blog Debugging stored procedures in SQL Server Management Studio (SSMS) to get more detail information. But this feature works with SSMS version 17.9.1 and earlier.


If the response is helpful, please click "Accept Answer" and upvote it, thank you.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.