SQL Stored Procedure Archives - Page 20 of 36 - SQL Authority with Pinal Dave

A team tree with Maya at level 1, Arjun and Lena at level 2 and Noor at level 3, beside four rowpatterns and their rows: the one-level path returns Maya, the two-level path returns Arjun and Lena, the three-level path returns Noor, and a double slash path returns all four

OPENXML Rowpattern in SQL Server: Search Every Level and Read the Parent

September 4, 2011

SQL Scripts, SQL Server, SQL Stored Procedure, SQL XML

An OPENXML rowpattern decides which XML elements become rows. This post tests absolute paths, a double slash that searches every level, and the ../ column pattern that reads the parent and grandparent. It repeats the result with nodes() and explains the flags value. Every result comes from SQL Server 2025.

Three steps as boxes: prepare returns a handle, OPENXML reads two rows through it, and release makes the handle unusable with message 8179, plus notes that the handle outlives the batch and that CATCH releases it

sp_xml_preparedocument: Prepare XML in Memory and Always Release It

September 3, 2011

Pinal Dave

SQL Tips and Tricks

SQL Memory, SQL Scripts, SQL Stored Procedure, SQL XML

sp_xml_preparedocument parses an XML document into memory and returns a handle that OPENXML can read. This post shows the safe pattern with TRY and CATCH, why the handle number is not yours to choose, what happens when you reuse a released handle, and how the WITH clause and flags shape the rows. Every result comes from SQL Server 2025.

Two columns of messages: errors SQL Server raises itself, 2714 and 515 at level 16 and 3701 at level 11, and errors you raise yourself with RAISERROR and THROW, 50000 and 50001 at level 16, plus a severity 10 note that is only a message

Raise Your Own Error in SQL Server: When Valid Code Breaks a Rule

August 27, 2011

Pinal Dave

SQL Tips and Tricks

SQL Error Messages, SQL Scripts, SQL Server, SQL Stored Procedure

Raise your own error in SQL Server when a valid statement breaks a rule you care about. This post shows three errors SQL Server raises itself, one error that depends on what exists in the database, and how RAISERROR and THROW turn an update of zero rows into a clear message. Every result comes from SQL Server 2025.

A table passed to a procedure in one call: the variable @List of type ClassListType holds three classes, the procedure AddClasses reads them through a READONLY parameter, and the Classes table ends with 5 rows

Pass a Table to a Stored Procedure With a Table Type

August 25, 2011

Pinal Dave

SQL Tips and Tricks

SQL Datatype, SQL Scripts, SQL Stored Procedure, SQL Variable

To pass a table to a stored procedure, you create a table type and declare a parameter of that type. This post builds a type, a procedure and a call that inserts three classes at once, then a call that inserts a thousand rows, and explains four common errors. Every result comes from SQL Server 2025.