/*
SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
		-- wszystkie dane pobrać przed transaction scope
		-- zweryfikuj dane przed rozpoczęciem transakcji
        BEGIN TRANSACTION;
        
		--kod tutaj update. insert

        COMMIT TRANSACTION;
		--RETURN 0
    END TRY
    BEGIN CATCH 

       IF @@TRANCOUNT > 0
       BEGIN
          ROLLBACK TRANSACTION
       END;
	   
	   --
       	DECLARE @cErrMsg NVARCHAR(2048)
		SET @cErrMsg= ERROR_MESSAGE()
		RAISERROR (@cErrMsg, 16, 1) WITH NOWAIT, SETERROR
		--RETURN(99)

    END CATCH;

*/
exec sp_addmessage
@msgnum = 50020,
@severity = 10,
@msgtext = 'Brak klienta w bazie spełniającego kryteria...',
@with_log = 'true'


SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
		-- wszystkie dane pobrać przed transaction scope
		-- zweryfikuj dane przed rozpoczęciem transakcji

		DECLARE @customerID		NCHAR(5) = NULL
	
		SELECT TOP 1
			@customerID = c.CustID
		FROM Sales.Orders o
		INNER JOIN  Sales.Customers c ON c.CustID = o.CustID
		--where c.custid = 100000
		GROUP BY 
			c.CustID,
			c.CompanyName
		ORDER BY COUNT(*) DESC

		IF(@customerID IS NULL)
		BEGIN
			RAISERROR(50020, 16, 1)
			--RETURN
		END
	
        BEGIN TRANSACTION;
        --kod tutaj update. insert

		UPDATE Sales.Customers SET CompanyName = CompanyName + 'xxx'
		WHERE custid = @customerID

		SELECT 1/0

		UPDATE Sales.Customers SET CompanyName = CompanyName + 'yyy'
		WHERE custid = @customerID

        COMMIT TRANSACTION;
		--RETURN 0
    END TRY
    BEGIN CATCH 

       IF @@TRANCOUNT > 0
       BEGIN
          ROLLBACK TRANSACTION
       END;
	   
	   --
       	DECLARE @cErrMsg NVARCHAR(2048)
		SET @cErrMsg= ERROR_MESSAGE()
		RAISERROR (@cErrMsg, 16, 1) WITH NOWAIT, SETERROR
		--RETURN(99)

    END CATCH;

SET NOCOUNT, XACT_ABORT OFF;


SELECT * from Sales.Customers where custid = 71

UPDATE Sales.Customers SET CompanyName = SUBSTRING(CompanyName, 0, 12) where custid = 71
if(@@ROWCOUNT = 1)
	PRINT 'SUPER zaktualizowano 1 wiersz'

SELECT 1/0
IF(@@ERROR <> 0)
BEGIN
	PRINT ERROR_MESSAGE()
END

SELECT TOP 10
	c.CustID
FROM Sales.Orders o
INNER JOIN  Sales.Customers c ON c.CustID = o.CustID
--where c.custid = 100000
GROUP BY 
	c.CustID,
	c.CompanyName
ORDER BY COUNT(*) DESC


IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO  
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
-- SELECT shows only keys 1 and 3 added.
-- Key 2 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
-- Key 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
SELECT *
 FROM t2;
GO