Saturday, January 4, 2014

Three ways to organize loop in SQL.

The exercise task is to come up with Stored Procedure that will print whole numbers starting from 0.

Here is the easiest one based on a While loop:



USE TEMPDB

GO
IF EXISTS (
SELECT TOP 1 1
FROM sys.procedures
WHERE name = 'usp_LoopTest'
)
DROP PROC usp_LoopTest
GO
CREATE PROC usp_LoopTest
@Param INT
as
DECLARE @i INT = 0;
WHILE @i <= @Param
BEGIN
  PRINT CAST(@i AS VARCHAR)
  SET @i += 1;
END
GO
EXEC usp_LoopTest 10
GO
DROP PROC usp_LoopTest
GO

The second shot is more complicated using operator GOTO:



USE TEMPDB
GO
IF EXISTS (
SELECT TOP 1 1
FROM sys.procedures
WHERE name = 'usp_LoopTest'
)
DROP PROC usp_LoopTest
GO
CREATE PROC usp_LoopTest
@Param INT
as
DECLARE @i INT = 0;
NoLoop:
PRINT CAST(@i AS VARCHAR)
SET @i += 1;
IF @i <= @Param GOTO NoLoop
GO
EXEC usp_LoopTest 20
GO
DROP PROC usp_LoopTest
GO


The third method is  not so obvious, it uses recursion:



USE TEMPDB
GO
IF EXISTS (
SELECT TOP 1 1
FROM sys.procedures
WHERE name = 'usp_LoopTest'
)
DROP PROC usp_LoopTest
GO
CREATE PROC usp_LoopTest
@Param INT
as
SET @Param -= 1;
IF @Param >= 0 EXEC usp_LoopTest @Param;
PRINT CAST(@Param + 1 AS VARCHAR)
GO
EXEC usp_LoopTest 30
GO
DROP PROC usp_LoopTest
GO


Only the problem with recursion - it does not go further than 32 levels deep.

Friday, January 3, 2014

How to Insert into a View and Detete from a View when it is not allowed.

At first, prepare tables and views:




USE tempdb;
GO

CREATE TABLE tbl_TestView_1(ID INT, F1 char(1));
GO
CREATE TABLE tbl_TestView_2(ID INT, F2 char(1));
GO
INSERT INTO tbl_TestView_1(ID, F1) VALUES(1,'A');
GO
INSERT INTO tbl_TestView_2(ID, F2) VALUES(1,'B');
GO
CREATE VIEW vw_TestView as
SELECT t1.ID, t1.F1, t2.F2
FROM tbl_TestView_1 as t1
INNER JOIN tbl_TestView_2 as t2
ON t1.ID = t2.ID;
GO
SELECT * FROM vw_TestView;
GO



Now try to Insert some values Into the View or Delete values from it:


INSERT INTO vw_TestView(ID, F1, F2)
VALUES(2, 'C','D'),(2, 'C','H');
GO
DELETE FROM vw_TestView
WHERE F1 = 'A';

GO
Here is the result:
Msg 4405, Level 16, State 1, Line 1
View or function 'vw_TestTriggerView' is not updatable because the modification affects multiple base tables.
Msg 4405, Level 16, State 1, Line 1
View or function 'vw_TestTriggerView' is not updatable because the modification affects multiple base tables.


Now will make a trick - create an INSTEAD OF Trigger:


CREATE TRIGGER trg_TestView on vw_TestView
INSTEAD OF INSERT
AS

BEGIN
INSERT INTO tbl_TestView_1(ID, F1)
SELECT DISTINCT ID, F1 FROM inserted;

INSERT INTO tbl_TestView_2(ID, F2)
SELECT DISTINCT ID, F2 FROM inserted;
END;
GO

Try to Insert:


INSERT INTO vw_TestView(ID, F1, F2)
VALUES(2, 'C','D'),(2, 'C','H');
GO
SELECT * FROM vw_TestView
GO
Here is the result:
ID          F1   F2
----------- ---- ----
1           A    B
2           C    D
2           C    H

 

Now modify trigger for Deletion:


ALTER TRIGGER trg_TestView on vw_TestView
INSTEAD OF INSERT, DELETE
AS

BEGIN
DELETE t FROM tbl_TestView_1 as t
INNER JOIN deleted as d
ON t.ID = d.ID and t.F1 = d.F1;

DELETE t FROM tbl_TestView_2 as t
INNER JOIN deleted as d
ON t.ID = d.ID and t.F2 = d.F2;

INSERT INTO tbl_TestView_1(ID, F1)
SELECT DISTINCT ID, F1 FROM inserted;

INSERT INTO tbl_TestView_2(ID, F2)
SELECT DISTINCT ID, F2 FROM inserted;
END;
GO

Try to Delete:


DELETE FROM vw_TestView 
WHERE F1 = 'A';
GO
SELECT * FROM vw_TestView
GO
Here is the result:
ID          F1   F2
----------- ---- ----
2           C    D
2           C    H





For a case when we do not need to delete records from the Parent table if the child table still have associated records we can modify trigger in this way:

ALTER TRIGGER trg_TestView on vw_TestView
INSTEAD OF INSERT, DELETE
AS

BEGIN
DELETE t FROM tbl_TestView_1 as t
INNER JOIN deleted as d ON t.ID = d.ID
WHERE NOT Exists (
SELECT TOP 1 1
FROM tbl_TestView_2 as t2
WHERE t2.ID = d.ID and t2.F2 != d.F2
);

DELETE t FROM tbl_TestView_2 as t
INNER JOIN deleted as d
ON t.ID = d.ID and t.F2 = d.F2;

INSERT INTO tbl_TestView_1(ID, F1)
SELECT DISTINCT ID, F1 FROM inserted;

INSERT INTO tbl_TestView_2(ID, F2)
SELECT DISTINCT ID, F2 FROM inserted;
END;
GO

Try to Delete:

DELETE FROM vw_TestView 
WHERE F2 = 'H';
GO
SELECT * FROM vw_TestView
GO
Here is the result:
ID          F1   F2
----------- ---- ----
2           C    D

Do not know if anybody will use this, but it is a fun feature.


Do not forget to cleanup at the end:



DROP TRIGGER trg_TestView;
GO
DROP VIEW vw_TestView;
GO
DROP TABLE tbl_TestView_1;
GO
DROP TABLE tbl_TestView_2;
GO