How to get a date in YYYY-MM-DD format from a TSQL datetime field?

[Origin]: https://stackoverflow.com/questions/889629/how-to-get-a-date-in-yyyy-mm-dd-format-from-a-tsql-datetime-field

How do I retrieve a date from SQL Server in YYYY-MM-DD format? I need this to work with SQL Server 2000 and up. Is there a simple way to perform this in SQL Server or would it be easier to convert it programatically after I retrieve the result set?

I’ve read the CAST and CONVERT on Microsoft Technet, but the format I want isn’t listed and changing the date format isn’t an option.

SELECT CONVERT(char(10), GetDate(),126)

Limiting the size of the varchar chops of the hour portion that you don’t want.

Starting with SQL Server 2012 (original question is for 2000):

SELECT FORMAT(GetDate(), 'yyyy-MM-dd')
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
answered Aug 30 ’16 at 6:53
Advertisements

Transposing Columns onto Rows

[Origin]: https://blogs.msdn.microsoft.com/benjones/2012/06/07/transposing-columns-onto-rows/

avatar of benjones

After a long period of absence, I have returned to posting some blog articles.  This one popped up last week from an Oracle DBA while I was onsite with a customer.  The conversation went something along the lines of:

Oracle DBA: “How can I place columns values on rows? I have a limited page width and need to alter the output”

Microsoftie “ah.. well, that’s easy..”

Consider the output below:

image

but we want it to appear like this:

image

The magic is really in the UNPIVOT function as shown below.

CREATE DATABASE sandbox;

USE sandbox;

CREATE TABLE tblPerson
(
    Email_Address varchar(50),
    First_Name varchar(50),
    Last_Name varchar(50)
);

INSERT INTO tblPerson VALUES
('ben@test.com', 'Ben', 'WJ')

SELECT * FROM tblPerson;

SELECT   
  tblPivot.Property, tblPivot.Value 
FROM   
  (SELECT
     CONVERT(sql_variant,Email_Address) AS Email_Address,
     CONVERT(sql_variant,First_Name) AS First_Name,
     CONVERT(sql_variant,Last_Name) AS Last_Name
   FROM tblPerson) Person
  UNPIVOT (Value For Property In (Email_Address, First_Name, Last_Name)) as tblPivot;

When to use SET vs SELECT when assigning values to variables in SQL Server

[Origin]: https://www.mssqltips.com/sqlservertip/1888/when-to-use-set-vs-select-when-assigning-values-to-variables-in-sql-server/

Problem

SET and SELECT may be used to assign values to variables through T-SQL. Both fulfill the task, but in some scenarios unexpected results may be produced. In this tip I elaborate on the considerations for choosing between the SET and SELECT methods for assigning a value to variable.

Solution

In most cases SET and SELECT may be used alternatively without any effect.

Following are some scenarios when consideration is required in choosing between SET or SELECT. Scripts using the AdventureWorks database are provided for further clarification.

Part 1 and 2 are mentioned in the scripts below. It would be better if you run each part of the script separately so you can see the results for each method.

Returning values through a query

Whenever you are assigning a query returned value to a variable, SET will accept and assign a scalar (single) value from a query. While SELECT could accept multiple returned values. But after accepting multiple values through a SELECT command you have no way to track which value is present in the variable. The last value returned in the list will populate the variable. Because of this situation it may lead to un-expected results as there would be no error or warning generated if multiple values were returned when using SELECT. So, if multiple values could be expected use the SET option with proper implementation of error handling mechanisms.

To further clarify the concept please run script # 1 in two separate parts to see the results

--Script# 1 - Using SET for assigning values
 
USE AdventureWorks
GO
-- Part1. Populate by single row through SET
DECLARE @Var1ForSet varchar(50)
SET @Var1ForSet = (SELECT [Name] FROM Production.Product WHERE ProductNumber = 'HY-1023-70')
PRINT @Var1ForSet
GO
-- Part 2. Populate by multiple rows through SET
DECLARE @Var2ForSet varchar(50)
SET @Var2ForSet = (SELECT [Name] FROM Production.Product WHERE Color = 'Silver')
PRINT @Var2ForSet
GO

Part 1 of the script should be successful. The variable is populated with a single value through SET. But in part 2 of the script the following error message will be produced and the SET statement will fail to populate the variable when more than one value is returned.

Error message generated for SET

Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Hence SET prevented assignment of an ambiguous value.

In case of SELECT, even if multiple values are returned by the query, no error will be generated and there will be no way to track that multiple values were returned and which value is present in the variable. This is demonstrated in the following script.

--Script # 2 - Using SELECT for assigning values

USE AdventureWorks
GO

-- Part1. Populate by single row through SELECT
DECLARE @Var1ForSelect varchar(50)
SET @Var1ForSelect = (SELECT [Name] FROM Production.Product WHERE ProductNumber = 'HY-1023-70')
PRINT @Var1ForSelect
GO

-- Part2. Populate by multiple rows through SELECT
DECLARE @Var2ForSelect varchar(50)
SELECT @Var2ForSelect = [Name] FROM Production.Product WHERE Color = 'Silver'
PRINT @Var2ForSelect
GO

Both part 1 and 2 were executed successfully. In part 2, multiple values have been assigned and accepted, without knowing which value would actually populate the variable. So when retrieval of multiple values is expected then consider the behavioral differences between SET and SELECT and implement proper error handling for these circumstances.

Assigning multiple values to multiple variables

If you have to populate multiple variables, instead of using separate SET statements each time consider using SELECT for populating all variables in a single statement. This can be used for populating variables directly or by selecting values from database.

Consider the following script comparing the use of SELECT and SET.

--Script # 3 - Populating multiple variables through SELECT

USE AdventureWorks
GO

-- Part 1. Assign direct values to multiple variables
DECLARE @var1 VARCHAR(50)
DECLARE @var2 VARCHAR(50)
DECLARE @var3 VARCHAR(50)

SELECT @var1 = 'Value1', @var2 = 'Value2', @var3 = 'Value3'
PRINT @var1
PRINT @var2
PRINT @var3
GO

-- Part 2. Assign retrieved values to multiple variables
DECLARE @name VARCHAR(50)
DECLARE @productNo VARCHAR(25)
DECLARE @color VARCHAR(15)

SELECT @name = [Name], @productNo = ProductNumber, @color = Color
FROM Production.Product 
WHERE ProductID = 320
PRINT @name
PRINT @productNo
PRINT @color
GO

If you are using SET then each variable would have to be assigned values individually through multiple statements as shown below.

--Script # 4 - Populating multiple variables through SET

USE AdventureWorks
GO

-- Part 1. Assign direct values to multiple variables
DECLARE @var1 VARCHAR(50)
DECLARE @var2 VARCHAR(50)
DECLARE @var3 VARCHAR(50)
SET @var1 = 'Value1'
SET @var2 = 'Value2'
SET @var3 = 'Value3'
PRINT @var1
PRINT @var2
PRINT @var3 
GO

-- Part 2. Assign retrieved values to multiple variables
DECLARE @name VARCHAR(50)
DECLARE @productNo VARCHAR(25)
DECLARE @color VARCHAR(15)
SET @name =(SELECT [Name] FROM Production.Product WHERE ProductID = 320)
SET @productNo = (SELECT ProductNumber FROM Production.Product WHERE ProductID = 320)
SET @color = (SELECT Color FROM Production.Product WHERE ProductID = 320)
PRINT @name
PRINT @productNo
PRINT @color
GO

Obviously SELECT is more efficient than SET while assigning values to multiple variables in terms of statements executed, code and network bytes.

What if variable is not populated successfully

If a variable is not successfully populated then behavior for SET and SELECT would be different. Failed assignment may be due to no result returned or any non-compatible value assigned to the variable. In this case, SELECT would preserve the previous value if any, where SET would assign NULL. Because of the difference functionality, both may lead to unexpected results and should be considered carefully.

This is shown in following script

--Script # 5 - Behavior of SET and SELECT for missing value

USE AdventureWorks
GO -- Part 1. Observe behavior of missing result with SET
DECLARE @var1 VARCHAR(20)
SET @var1 = 'Value 1 Assigned'
PRINT @var1
SET @var1 = (SELECT Color FROM Production.Product WHERE ProductID = 32022)
PRINT @var1
GO 

-- Part 1. Observe behavior of missing result with SELECT
DECLARE @var1 VARCHAR(20)
SELECT @var1 = 'Value 1 Assigned'
PRINT @var1 
SELECT @var1 = Color FROM Production.Product WHERE ProductID = 32023
PRINT @var1
GO

We can see that part 1 generates NULL when no value is returned for populating variable. Where as part 2 produces the previous value that is preserved after failed assignment of the variable. This situation may lead to unexpected results and requires consideration.

Following the standards

Using SELECT may look like a better choice in specific scenarios, but be aware that using SELECT for assigning values to variables is not included in the ANSI standards. If you are following standards for code migration purposes, then avoid using SELECT and use SET instead.

Conclusion

Best practice suggests not to stick to one method. Depending on the scenario you may want to use both SET or SELECT.

Following are few scenarios for using SET

  • If you are required to assign a single value directly to variable and no query is involved to fetch value
  • NULL assignments are expected (NULL returned in result set)
  • Standards are meant to be follow for any planned migration
  • Non scalar results are expected and are required to be handled

Using SELECT is efficient and flexible in the following few cases.

  • Multiple variables are being populated by assigning values directly
  • Multiple variables are being populated by single source (table , view)
  • Less coding for assigning multiple variables
  • Use this if you need to get @@ROWCOUNT and @ERROR for last statement executed

SQL Server Reporting Services Using Multi-value Parameters

[Origin]: https://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/

Problem

After working with SQL Server Reporting Services ( SSRS ) for a time, eventually a report user will want to select more than one value for a parameter. How does SSRS handle multi-value parameters?

Solution

Allowing users to select multiple values from a parameter list is a great feature in SSRS; it allows report consumers more selection flexibility when running reports. However, how the parameters are passed to the dataset (or report object filters if used) varies depending on if the datasource is based on T-SQL embedded in a dataset within a report or if the data is passed via a stored procedure. Furthermore, once the report is run, a good practice is to display the selected parameter list somewhere within the report. The below instructions will convey the various techniques needed to utilize multi-value parameters. Finally, we will cover using a filter based multi-value parameters.

Embedded Parameters

Of the various options, passing multi value parameters to an embedded query is the less complex of the two methods. Utilizing this method, the report designer just needs to write a normal query, and utilize the “IN” key word in the criteria section of the query and then refer to the multi-value parameter using the @parameter name syntax within the parentheses portion of the IN statement. Using an AdventureWorks database and report example, the below code, inserted into a report dataset, notates the required syntax. This syntax should be somewhat standard to many of you who write T-SQL on a daily basis.

SELECT P.FirstName + ' ' + P.LastName AS Employee
    ,DATEPART(Year, SOH.OrderDate) AS Year
    ,DATEPART(Month, SOH.OrderDate) AS MonthNumber
    ,DATENAME(Month, SOH.OrderDate) AS Month
    ,SUM(DET.LineTotal) AS Sales
FROM Sales.SalesPerson AS SP
INNER JOIN Sales.SalesOrderHeader AS SOH ON SP.BusinessEntityID = SOH.SalesPersonID
INNER JOIN Sales.SalesOrderDetail AS DET ON SOH.SalesOrderID = DET.SalesOrderID
INNER JOIN Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID
INNER JOIN HumanResources.Employee AS E ON SOH.SalesPersonID = E.BusinessEntityID
INNER JOIN Person.Person AS P ON P.BusinessEntityID = SP.BusinessEntityID
WHERE (YEAR(SOH.OrderDate) IN (@ReportYear))
GROUP BY P.FirstName + ' ' + P.LastName
    ,SOH.SalesPersonID
    ,DATEPART(Year, SOH.OrderDate)
    ,DATEPART(Month, SOH.OrderDate)
    ,DATENAME(Month, SOH.OrderDate)

Next we will setup the parameter to accept multiple values. In the below example, a parameter called @ReportYear is already created, so right mouse clicking on the parameter (Report Year in the below example ) and selecting Parameter Properties will open the Report Parameter Properties window. Now check the Allow multiple values option. If you are setting up a new parameter, right mouse click on Parameters and then select New Parameter.

parameterspec
parameter property

Subsequently, we will define the values to be used for our parameter list; this list will be the values presented to the report consumer. First, we define a dataset using the following simple query to generate a list of values for the ReportYear field.

SELECT
Year(SOH.OrderDate) AS Year
FROM
Sales.SalesOrderHeader AS SOH
GROUP BY
Year(SOH.OrderDate)
ORDER BY SOH.Year

 

Year Lookup DataSet

Finally, we set the dataset, Year_Lookup, to be used for the available values for the ReportYear parameter, and note below.

ParameterAvailableValues

Now, the parameter is setup to accept multiple values. The setup required several steps including setting up our main report query to accept a parameter using the IN criteria, changing the allow multiple values option on the parameter properties, and last, generating a list of available values, in this example using another query. The end result is two fold. First, the report consumer now sees check boxes next to each parameter item which allows the user to select multiple values, as displayed below. Second, the report displays only the years selected.

Parameter List

Finally, the report data displays the years selected.

Final Report

You may notice in the above figure that the title shows #Error after Sales Report for:.This field references the @ReportYear parameter; when this parameter was just a single value, it displayed correctly. However, now that the parameter is multiple values, SSRS is unable to display a value. Fortunately, the SSRS Join function can be used to display all the values selected and separate them with a fixed symbol. For this report we will break up the years with an &. The exact formula used is as follows:

=JOIN(Parameters!ReportYear.Value, ” & “)

The report with the JOIN function utilized is displayed below.

Join Function

Multiple Value Parameters and Stored Procedure Based Datasets

Using stored procedures as the basis for SSRS datasets offers many advantages including potential reuse by other reports and potential performance advantages. However, multi-value parameters do not work well when getting passed to a stored procedure. Embedded SQL datasets noted above handle the parsing of the multiple values used in the IN criteria. To the contrary, when the multiple values are passed to a stored procedure, all the values are conveyed as one value.

The ReportYear parameter in our example report, for instance, would get passed as one value, “2006,2007,2008” which, of course would return no rows. Fortunately, we can use a string splitter function as part of our stored procedure to break up the years into multiple values. We will once again turn to using a Tally table by Jeff Moden; please see this article on using the Tally table to parse out the values, http://www.sqlservercentral.com/articles/Tally+Table/72993/.

I am not going to repeat Mr. Moden’s code in the article, since it would be beneficial for you to understand what it can and cannot do. We will however use this function in dataset stored procedure which is noted below. Please make the following notes about the stored procedure. First we are passing in the concatenated multi-value parameter as a single entity and we use a varchar parameter (which must be large enough to accept the maximum length of all the parameters which might be selected). Next using the splitter function, the values are parsed out and placed into a temporary table, #YEAR_LIST. Last, the year criteria is moved from being part of the where clause to being part of the joins.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Scott Murray
-- Create date: 01/01/2013
-- Description: Sales by year query with parameter breakout
-- =============================================
ALTER PROCEDURE dbo.usp_Sales_by_Year 
 @ReportYear varchar(50)

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

--Parse values into table which will be an inner join on main data query.
SELECT Item
INTO #YEAR_LIST 
FROM
dbo.DelimitedSplit8K(@ReportYear,',')

--Main Dataset 

SELECT P.FirstName + ' ' + P.LastName AS Employee
 ,DATEPART(Year, SOH.OrderDate) AS Year
 ,DATEPART(Month, SOH.OrderDate) AS MonthNumber
 ,DATENAME(Month, SOH.OrderDate) AS Month
 ,SUM(DET.LineTotal) AS Sales
FROM Sales.SalesPerson AS SP
INNER JOIN Sales.SalesOrderHeader AS SOH ON SP.BusinessEntityID = SOH.SalesPersonID
INNER JOIN Sales.SalesOrderDetail AS DET ON SOH.SalesOrderID = DET.SalesOrderID
INNER JOIN Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID
INNER JOIN HumanResources.Employee AS E ON SOH.SalesPersonID = E.BusinessEntityID
INNER JOIN Person.Person AS P ON P.BusinessEntityID = SP.BusinessEntityID
INNER JOIN #YEAR_LIST AS YEARLIST ON YEAR(SOH.OrderDate) = YEARLIST.Item --Use join instead of where clause
--WHERE (YEAR(SOH.OrderDate) IN (@ReportYear))
GROUP BY P.FirstName + ' ' + P.LastName
 ,SOH.SalesPersonID
 ,DATEPART(Year, SOH.OrderDate)
 ,DATEPART(Month, SOH.OrderDate)
 ,DATENAME(Month, SOH.OrderDate)

END
GO

Certainly other methods exists to handle the parsing and include using the function in the where clause (I would avoid this method as I would not want to call this function for every row!). Alternatively, you could use a cross apply to match the years with the dataset. The final report utilizing the stored procedure methods is displayed subsequently.

Report Final Stored Procedure

Using Filters with Multiple Value Parameters

One last alternative involving the use of parameters pertains to dataset or object filters. Using filters at the object level, for example on a tablix, actually allows the same “larger” dataset to be used for multiple purposes while at the same time filtering the individual report parts based on a particular criteria. This setup can be advantageous in using a single dataset for all the report data; however, you also need to be careful about retrieving “very large” datasets while only using very small sets of the data. To setup a filter, first select the object in question, and then right mouse, click and select properties as illustrated below.

Filter Prpperties

On the properties window, select the Filter window, and click add. Select the “field” or expression that is to be filtered, and then select the “In” Operator. Last, click on the fx expression builder button to the right of the value field to open the expression builder box.

Filter Properties Settings

As shown in the below illustration, within the filter expression box, click on Parameters under Category, and then double click on ReportYear Under years. What appears in the expression value box includes “(0)” at the end of name. This zero actually means retrieve the parameter in ordinal position 0 (ie the first parameter of the selected parameters). As you are probably realizing, that is not what we would like to filter on; we want to filter on all the parameter values selected. The trick to make the filter work, is as easy as removing the “(0)” from the expression.

Filter Parameter Screen 1

Finally, the filter expression value actually should look as displayed below.

Filter Parameter Screen 2

Now, the resulting report using a multiple value tablix filter is illustrated below.

Final Report Filter

Conclusion

Multiple value parameters are a wonderful tool within SSRS; their methods in practice, though, varies depending on their usage within embedded T-SQL, within stored procedures, or as part of an SSRS object filter. Embedded T-SQL is somewhat easier to use, however, the query can not be easily shared; to the contrary, using a stored procedure offers the ability to reuse a query (and other set based and logic structures), but you must parse parameter. Using a multiple value parameter with an object filter is also easy to implement as long as you know how to implement the parameter values in the filter. Last, it is often beneficial to display the parameters selected by the report consumer; the join function in SSRS allows you to display the parameter list easily in the report header or body.

SQL Server – SELECT FROM stored procedure

[Origin]: https://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure

I have a stored procedure that returns rows:

CREATE PROCEDURE MyProc
AS
BEGIN
    SELECT * FROM MyTable
END

My actual procedure is a little more complicated, which is why a sproc is necessary.

Is it possible to select the output by calling this procedure?

Something like:

SELECT * FROM (EXEC MyProc) AS TEMP

I need to use SELECT TOP XROW_NUMBER, and an additional WHERE clause to page my data, and I don’t really want to pass these values as parameters.

You should look at this excellent article by Erland Sommarskog:

It basically lists all available options for your scenario.

You can

  1. create a table variable to hold the result set from the stored proc and then
  2. insert the output of the stored proc into the table variable, and then
  3. use the table variable exactly as you would any other table…

… sql ….

Declare @T Table ([column definitions here])
Insert @T Exec storedProcname params 
Select * from @T Where ...

You either want a Table-Valued function or insert your EXEC into a temporary table:

INSERT INTO #tab EXEC MyProc

The specified cast from a materialized ‘System.Int32’ type to the ‘System.Boolean’ type is not valid.

This error may raised in the following scenario:

  1. call the stored procedure to return JSON through Web API
  2. the stored procedure contain multiple select statements
  3. the data returned will be whatever the value that is retrieved from the first SELECT statement encountered
  4. thus, causing cast issue since the date type may not be the one the caller expects

Solution: make sure that only the last SELECT statement is used to retrieved the return result value (e.g. JSON string), for any other SELECT statement required, declare @variables to temporarily hold the scalar-value in the SELECT statement instead.

SQL Server transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

[Origin]: https://www.mssqltips.com/sqlservertip/4018/sql-server-transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-commit-statements/

Problem

There are situations where you might receive this error message “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.”. Even when using a TRY CATCH block in the stored procedure and a ROLLBACK in the catch block, this error still occurs. One reason this is not captured is because fatal errors are not caught and the transaction remains open. In this tip we will look at how you can handle this issue.

Solution

We will look at an example to create this issue and how this can be resolved. This example is probably something you wouldn’t normally do, but this allowed me to show the issue with a real working example.

We create two different global temporary tables in different query windows:

--On Query Window 1
CREATE TABLE ##tmpStagingTest1
(
    ID INT IDENTITY(1,1),
    Spid SMALLINT,
    Value INT 
)

--On Query Window 2
CREATE TABLE ##tmpStagingTest2
(
    ID INT IDENTITY(1,1),
    Spid SMALLINT,
    Value INT UNIQUE
)

We then create the following stored procedure which works with these tables:

CREATE PROCEDURE uspInsertStageingDataTest
@pValue INT
AS
BEGIN

    SET NOCOUNT ON

    BEGIN TRY

        BEGIN TRANSACTION

        INSERT INTO ##tmpStagingTest1(Spid, Value)
        VALUES (@@SPID, @pValue)

        INSERT INTO ##tmpStagingTest2(Spid, Value)
        VALUES (@@SPID, @pValue)

        COMMIT

    END TRY
    BEGIN CATCH

        SELECT ERROR_MESSAGE()

        IF @@TRANCOUNT > 0
            ROLLBACK

    END CATCH

END

Without closing “Query Window 1” and “Query Window 2” (to make sure the global temporary tables exist) we open a new “Query Window 3” and execute the procedure:

EXEC uspInsertStageingDataTest @pValue=1

There are no errors and the SP runs successfully.

We can then check the data in the tables and the transaction count as follows running the below in “Query Window 3”:

SELECT * FROM ##tmpStagingTest1
SELECT * FROM ##tmpStagingTest2

SELECT @@TRANCOUNT AS Trancount

The result is the following: one row was inserted into each table and the transaction has committed:

SET_XACT_ABORT_ON

Now let’s close “Query Window 2”, so temporary table ##tmpStagingTest2 is deleted and run the uspInsertStageingDataTest stored procedure again:

EXEC uspInsertStageingDataTest @pValue=2

We then get the following error message:

uspInsertStageingDataTest

If we execute these statements again the results are as follows:

SELECT * FROM ##tmpStagingTest1
SELECT * FROM ##tmpStagingTest2

SELECT @@TRANCOUNT AS Trancount

The first statement returns two rows.

result_of_these_queries

The second statement returns an error.

transaction_count

And the third statement returns a Trancount of 1:

SELECT_@@TRANCOUNT

As we can see, one row was inserted in the first table, then the procedure failed and the transaction has not been committed, because an error has not been caught. This is a problem, because we want to implement the logic which inserts data into both tables or insert nothing. We can manually execute the rollback command in “Query Window 3”, after which the transaction will be rolled back and only the first row will remain in the ##tmpStagingTest1 table. If we try to close “Query Window 3” without doing a ROLLBACK we will receive the following message:

Query_Window

Solving The Issue

Let’s assume that we manually rolled back the transaction and there is only one row in ##tmpStagingTest1 table. The reason why the transaction remains open is that ##tmpStagingTest2 table is missing and it causes the fatal error. The catch block can’t catch fatal errors, so the transaction remains open.

To avoid similar situations and implement our logic without problems we can set XACT_ABORT ON in our procedure. When SET XACT_ABORT is ON and T-SQL statement raises a run-time error, SQL Server automatically rolls back the current transaction. By default XACT_ABORT is OFF. Let’s use this in our stored procedure as follows:

ALTER PROCEDURE uspInsertStageingDataTest
@pValue INT
AS
BEGIN

    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRY
        BEGIN TRANSACTION

        INSERT INTO ##tmpStagingTest1(Spid, Value)
        VALUES (@@SPID, @pValue)

        INSERT INTO ##tmpStagingTest2(Spid, Value)
        VALUES (@@SPID, @pValue)

        COMMIT
    END TRY
    BEGIN CATCH

        SELECT ERROR_MESSAGE()

        IF @@TRANCOUNT&gt;0
            ROLLBACK

    END CATCH

END

Then we run it again:

EXEC uspInsertStageingDataTest @pValue=3

We will receive the following message:

SELECT_ERROR_MESSAGE()

But in this case transaction is rolled back and the trancount is 0:

SELECT @@TRANCOUNT AS Trancount
SELECT_@@TRANCOUN

Now if we create the ##tmpStagingTest2 table again and run this command twice:

EXEC uspInsertStageingDataTest @pValue=3

The first time it completes successfully, because there is unique constraint on “Value” column in ##tmpStagingTest2 table, the second execution of the stored procedure returns the following message:

##tmpStagingTest2

This means that this error isn’t a fatal error and is handled in the catch block. We return ERROR_MESSAGE() which is selected in the catch block and the transaction is rolled back in the catch block. If we check the trancount we can see this value is 0.

SELECT @@TRANCOUNT AS Trancount
TRY_CATCH_blocks

When SET XACT_ABORT ON is included in a stored procedure without using TRY/CATCH blocks, transactions will roll back in case of errors. However, usually it’s necessary to handle errors in a specific way, for example returning the corresponding message or assign a corresponding response code to output variable when errors occur (not fatal errors). This kind of error handling can be developed in the CATCH block, so using SET XACT_ABORT ON with TRY/CATCH gives us a more flexible error handling solution.

Conclusion

Using SET XACT_ABORT ON in stored procedures can be very helpful in situations when there are transactions and the possibility of fatal errors. It helps developers avoid uncommitted transactions. Using SET XACT_ABORT ON in procedures with TRY/CATCH blocks, we should take into account the fact that all errors that are caught in the catch block are handled there. Only in cases when the CATCH block is unable to handle the error, thanks to the fact that XACT_ ABORT is ON, SQL Server rolls back the transaction.