Stored Procedures

First specify the database the stored procedure applies to, set the encoding and set the QUOTED_IDENTIFIER to enable. Basically the 'ANSI_NULLS ON' instruction causes SQL Server to process NULL as its own type, instead of as a variable assigned to something. In future versions of SQL Server, ANSI_NULLS might be set by default. The 'SET QUOTED_IDENTIFIER ON' instruction causes the database server to handle anything in quote as identifiers, instead of as literal statements.


				USE [DRS]

				GO

				/****** Object:  StoredProcedure [dbo].[prGetReport]    Script Date: 06/07/2017 11:13:39 ******/

				SET ANSI_NULLS ON

				GO

				SET QUOTED_IDENTIFIER ON

				GO

Next we define the stored procedure name, e.g. 'prGetReport' as a database object (.dbo), and define the input parameters and their types.

ALTER PROCEDURE [dbo].[prGetReport]
@reportId BIGINT,
@ExcludePatientData BIT = 0,
@ProfileReadCode VARCHAR(50) = NULL,
@_debug TINYINT = 0 The 'BEGIN TRY' statement is important, as it simulates the changes without applying them until a 'COMMIT' command is issued. his is why it's called 'Transact SQL'. We set 'XACT_ABORT ON' to abort the operation if there's an error.


				AS

				SET NOCOUNT, XACT_ABORT ON;

				BEGIN TRY

Following this, there might be a large collection of variable declarations. The following is an abbreviated version. One set assigns variables (column header names?) to parameters:


				DECLARE @ReportSummary xml_ReportSummary

				DECLARE @SubjectIdentifier SubjectIdentifierType

				DECLARE @PatientVisit XML_PatientVisit

				DECLARE @ReportComments XML_ReportComments

I guess it's possible, as with C#, to set a variable as a table:


				DECLARE @ObservationResultComments TABLE ( ObservationRequestId BIGINT NULL, ObservationResultId BIGINT NULL, Comment VARCHAR(max) NULL, Node BIGINT NULL, orderbyid int)

And there are examples of parameter typing:


				DECLARE @ProviderDepartment VARCHAR(50), @ProviderDepartmentCode VARCHAR(50), @SubjectLocation VARCHAR(50),

This seems to be formatting the stored procedure response as an XML message, using a schema from an XML namespace (xmlns).


				EXEC @PrepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @ReportXML, ''

Stored Procedures

JOIN and UNION Statements

References