Lab7_BD

GO
USE master
GO
IF EXISTS (SELECT * FROM sys.DATABASES WHERE name='moedata')
        DROP DATABASE moedata
GO
CREATE DATABASE moedata
GO
ALTER DATABASE moedata SET MULTI_USER
GO
USE moedata
GO
CREATE TABLE Street
(StreetId INT NOT NULL IDENTITY(1,1)PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
----GO
INSERT INTO Street(Name)
                 VALUES ('Пушкинская')
INSERT INTO Street(Name)
                 VALUES ('Лермонтовская')
INSERT INTO Street(Name)
                 VALUES ('Чуковская')
INSERT INTO Street(Name)
                 VALUES ('Толкиенская')
--GO
----SELECT * FROM Street
----GO
CREATE TABLE Fam
(FamId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
INSERT INTO Fam(Name)
         VALUES ('Яблочных')
INSERT INTO Fam(Name)
         VALUES ('Апельсинов')
INSERT INTO Fam(Name)
         VALUES ('Виноградов')
INSERT INTO Fam(Name)
         VALUES ('Грушев')
INSERT INTO Fam(Name)
         VALUES ('Арбузова')
--GO
----SELECT * FROM Fam
--GO
CREATE TABLE Subscriber
(SubscriberId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
PhoneNumber nvarchar(9) NOT NULL,
FamId INT NOT NULL,
Iniz nvarchar(4) NOT NULL,
Indeks nvarchar(5) NOT NULL,
StreetId INT NOT NULL,
HouseNumber INT NOT NULL,
FlatNumber INT NOT NULL,
Sex nvarchar(1) NOT NULL,
FOREIGN KEY (FamID) REFERENCES Fam(FamId),
FOREIGN KEY (StreetId) REFERENCES Street(StreetId))
GO
INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES ('745-34-19',1, 'А.А.','61005',2, 13,3,'М')
INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES ('234-43-53',2, 'Р.В', '61005', 1,1,45,'М')
         GO
IF EXISTS( SELECT name FROM sys.Objects WHERE name='Proc1' AND TYPE='p')
DROP PROCEDURE Proc1
GO

CREATE PROCEDURE Proc1 @FamIdX1 INT, @NameX1 nvarchar(25),
@SubscriberIdX INT, @PhoneNumberX nvarchar(9), @FamIdX3 INT,
@InizX nvarchar(4),@IndeksX nvarchar(5),@StreetIdX3 INT, @HouseNumberX INT,
 @FlatNumberX INT, @SexX nvarchar(1), @StreetIdX2 INT, @NameX2 nvarchar(25)
AS
    BEGIN TRANSACTION
        IF NOT EXISTS(SELECT StreetId FROM Street WHERE name=@NameX2)
        INSERT INTO Street(Name)
                 VALUES (@NameX2)
        DECLARE @St INT
        SELECT @St=StreetId FROM Street WHERE name=@NameX2

        DECLARE @F INT
        IF NOT EXISTS(SELECT * FROM Fam WHERE name=@NameX1)
        INSERT INTO Fam(Name)
                 VALUES (@NameX1)
        SELECT @F=FamId FROM Fam WHERE name=@NameX1

        IF NOT EXISTS(SELECT * FROM Subscriber
        WHERE PhoneNumber=@PhoneNumberX AND Iniz=@InizX AND
        Sex=@SexX AND indeks=@IndeksX AND FamId=@F AND StreetId=@St)

        INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
                 VALUES (@PhoneNumberX, @F, @InizX,@IndeksX ,@St, @HouseNumberX,@FlatNumberX,@SexX)
        DECLARE @a INT
        DECLARE @res INT
        SET @a =CONVERT(INT,RAND()*5+1)
        IF(@a=2)
        BEGIN
            ROLLBACK
            SET @res=0
            RETURN @res
        END
        ELSE
        BEGIN
            commit TRANSACTION
            SET @res=1
            RETURN @res
        END
GO
IF OBJECT_ID(N'dbo.view1') IS NOT NULL
DROP VIEW dbo.view1
GO
CREATE VIEW view1
AS
    SELECT Fam.Name,Subscriber.HouseNumber,Subscriber.Indeks FROM Subscriber
    INNER JOIN Fam
    ON Fam.FamId=Subscriber.FamId
    INNER JOIN Street
    ON Street.StreetId=Subscriber.StreetId
GO
DECLARE @b INT
EXECUTE @b=Proc1 1,'Пушкинская',3,'745-34-19',1, 'А.А.','61005',1, 13,3,'М',1,'Яблочных'
IF @b=1
    print('Good')
IF @b=0
    print('Bad')
SELECT * FROM view1