4
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.
param (
...
4
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 ...
3
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.
--demo setup
drop procedure if exists [dbo].[sp1]
go
CREATE PROCEDURE [dbo].[sp1] @P1 INT
,@P2 INT
AS
BEGIN
SELECT @p1 AS p1
,@p2 ...
2
The equivalent of Oracle's anonymous PL/SQL blocks are anonymous PL/pgSQL blocks in Postgres. These are started using the do statement:
do
$$
declare
....
begin
....
end;
$$
2
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....
1
SQL Server will execute the statements in a batch one at a time, from top to bottom. Each statement will complete before the next is executed. In your case the this means the first SP runs to completion, then the second one starts and runs to completion, then the third starts and runs to completion. There is no circumstance in which anything else can occur.
...
1
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
