Here is an example of inserting the results of a store procedure call into a table variable (of course,you can use a real table). The stored procedure takes 2 parameters and returns their values as a result set.
drop procedure if exists [dbo].[sp1]
CREATE PROCEDURE [dbo].[sp1] @P1 INT
SELECT @p1 AS p1
It's like most dbms features, if you use it in the right situation it does it's job well, the wrong situation it does it poorly.
Pros: Some things just can't be done without it. Typically I have only found this to be for administrative work, and not application code. Some system commands don't allow for parameters to be used as input. So for example if I ...
Do not use Dynamic SQL.
99% of the time Dynamic SQL is used due to the lack of knowledge on how to use optional parameters in stored procedures, the rest 1% of the time is used to create a highly complex query for a report that the customer doesn't understand even. The Curse and Blessings of Dynamic SQL doesn't show an example of why it would be a good idea ...
Yes it is true that you should not change an actively running stored procedure, because SQL Server doesn't like it.
As for taking downtime, well yeah, you shouldn't be making changes to frequently-used stored procedures in a busy production environment during operating hours as a best-practice.
Digging in the mysql schema (USE mysql; SHOW TABLES;) I found the procs_priv table that contains the information I need:
SELECT Routine_type, Routine_name, User, Host
WHERE Db = DATABASE()
AND Proc_priv = 'Execute';
The problem you're facing is down to how the TRY...CATCH construct handles errors - it consumes them, eats them, and spits nothing back out except for a 0 return code. This is evidenced by a very innocuous line in the docs on TRY...CATCH that says
Errors trapped by a CATCH block are not returned to the calling application. If any part of the error ...
What version of MySQL?
(At least) before 8.0, this will list users that have "Execute" privilege in all databases:
SELECT user, host FROM mysql.user WHERE Execute_priv = 'Y';
This will say which ones have permission in a particular my_db:
SELECT user, host FROM mysql.user WHERE Execute_priv = 'Y' AND db = "my_db";
(The following probably works in all ...
Here is a solution that I put together that worked, at least in my testing.
It relies on dbo.DelimitedSplit8K by Jeff Moden which you can find here https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function
You just need to change it to use sys.sql_modules to sub in for the procname and proccode portions....
Below is a PowerShell example that uses the Microsoft.SqlServer.TransactSql.ScriptDom to parse procs and identify those with BEGIN TRAN statements. This version will download the assembly from NuGet if Microsoft.SqlServer.TransactSql.ScriptDom.dll doesn't already exist in the specified location. You could use a package manager instead for that task.
As far as I know session started with SQL Server authentication will not let you create security context outside of SQL Server (because the only external context available is SQL Server service account and EXECUTE AS will not let you impersonate SQL Server service account for external access).
SSISDB procedures has to have context outside of SQL Server, so ...
There is a DMV called sys.dm_sql_referenced_entities. It returns a column is_insert_all documented thus
1 = The object is used in an INSERT statement without a column list (object-level only).
This seems to be what you're looking for.
Here's an example of it in practice. First we'll create a simple one-column table
drop table if exists dbo.T1;