Why does SQL Server say a procedure attempted to return a status of NULL and returns 0 instead? A reader asked on Facebook, and I explain what it means.
How to Pass Stored Procedure Result to Another Procedure in SQL Server
To pass stored procedure result values from one procedure to another, use an OUTPUT parameter. This post tests RETURN codes, INSERT … EXEC and table-valued parameters on a small order demo. It also shows the errors each method raises and when a function is the better tool.
Temp Table Scope in Nested Procedures and Dynamic SQL
Explain temp table scope across nested procedures and dynamic SQL, reproduce missing and duplicate-name errors, and pass rows safely.
Finding Procedures Created With the Wrong SET Options
Find procedures with wrong SET options in sys.sql_modules, understand error 1934, and recreate modules with ANSI_NULLS and QUOTED_IDENTIFIER ON.
Safe Dynamic SQL With sp_executesql and QUOTENAME
Write safe dynamic SQL with typed value parameters, validated object names, and allowed sort columns, then test hostile input.
SQL SERVER – Significance of Table Input Parameter to Stored Procedure
Pass a whole table into a stored procedure instead of forming and parsing XML. Here is why a table input parameter to stored procedure saves round trips.
Cursor Options That Matter: LOCAL, FAST_FORWARD and STATIC
Test SQL Server cursor options, including LOCAL FAST_FORWARD and STATIC, on one loop while measuring time and tempdb cost.