-
Notifications
You must be signed in to change notification settings - Fork 0
/
PROJECTquery.sql
221 lines (182 loc) · 6.82 KB
/
PROJECTquery.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
use DB03TMS24_1819
create table logincastle_3 (loginid int primary key identity(1,1),username varchar(20),password varchar(20),timestamp datetime )
insert into logincastle_3 values('primeadmin','Access@333',null)
insert into logincastle_3 values('customer','Cust@333',null)
select * from logincastle_3
exec sp_helptext update_customer
alter proc updatecastle(@custid int out, @custSsnId bigint,@custName varchar(20),@Age int,@Address1 varchar(20),
@Address2 varchar(20),@city varchar(20), @state varchar(20)) as
begin
declare @tmp datetime
set @tmp=current_timestamp
update Custdet_1288 set custName=@custName,Age=@Age,Address1=@Address1,Address2=@Address2,city=@city,state=@state,lastupdated=@tmp
where custSsnId=@custSsnId
set @custid=@@identity
end
select * from
Custdet_1288
alter proc update_customer(@custid int out, @custSsnId bigint,@custName varchar(20),@Age int,@Address1 varchar(20),
@Address2 varchar(20),@city varchar(20), @state varchar(20)) as
begin
declare @tmp datetime
set @tmp=current_timestamp
update Custdet_1288 set custName=@custName,Age=@Age,Address1=@Address1,Address2=@Address2,city=@city,state=@state,lastupdated=@tmp
where custSsnId=@custSsnId
set @custid=@@identity
end
drop table castleaccount33
select * from castleaccount33
create table castleaccount33(AccountId int primary key identity (5555,1),CustomerId int foreign key references Custdet_1288(custid),AccountType varchar(20)
,DepositAmmount int ,lastupdated datetime,ShowMessage varchar(50),AccountStatus varchar(20))
alter proc addaccount33 (@CustomerId int,@AccountType varchar(20),@ShowMessage varchar(50),@AccountStatus varchar(20),@Balance int,@flagg int out) as
begin
declare @tmp datetime
declare @acsts varchar(20)
set @acsts =(select AccountStatus from Custdet_1288 where custid =@CustomerId)
set @tmp=current_timestamp
declare @aid int
set @aid =(select count(*) from castleaccount33 where CustomerId =@CustomerId and AccountType=@AccountType)
if (@aid<1)
begin
if(@acsts='Active')
begin
insert into castleaccount33(CustomerId ,AccountType , lastupdated,ShowMessage ,AccountStatus,Balance ) values (@CustomerId ,@AccountType ,@tmp,@ShowMessage ,@AccountStatus,@Balance )
set @flagg=1
end
else
begin
set @flagg=0
end
end
else
begin
set @flagg=2
end
print @flagg
end
exec addaccount33 1026,'Current','success','Active',5555,null
select * from transaction33
select * from Custdet_1288
select * from castleaccount33
alter proc deleteaccount33(@AccountId int ) as
begin
declare @tmp datetime
set @tmp=current_timestamp
update castleaccount33 set AccountStatus='Inactive',ShowMessage ='Account deactivated',lastupdated=@tmp
where AccountId=@AccountId
end
ALTER TABLE Custdet_1288
ADD lastupdated datetime,DisplayMessage varchar(50),AccountStatus varchar(20)
select * from
Custdet_1288
select * from
castleaccount33
alter proc sp_Custdet_1288(@custid int out, @custSsnId bigint,@custName varchar(20),@Age int,@Address1 varchar(20),
@Address2 varchar(20),@city varchar(20), @state varchar(20),@DisplayMessage varchar(50),@AccountStatus varchar(20))
as
begin
declare @tmstp datetime
set @tmstp=current_timestamp
insert into Custdet_1288(custSsnId,custName,Age,Address1,Address2,city,state,lastupdated,DisplayMessage ,AccountStatus) values(@custSsnId, @custName, @Age, @Address1 ,@Address2 ,@city, @state,@tmstp,@DisplayMessage ,@AccountStatus )
set @custid=@@identity
end
exec sp_helptext delete_castle
alter proc delete_castle(@custid int )
as
begin
declare @tmstp datetime
set @tmstp=current_timestamp
update Custdet_1288 set AccountStatus='Inactive',DisplayMessage ='Customer deactivated',lastupdated=@tmstp
WHERE custid = @custid
end
alter table castleaccount33 drop column DepositAmmount
alter table castleaccount33 add Balance int
select * from castleaccount33
alter proc deposit(@AccountId int,@ShowMessage varchar(50),@depositamount int,@TransactionId int out) as
begin
declare @tmpstp datetime
set @tmpstp=CURRENT_TIMESTAMP
declare @bal int
set @bal =(select Balance from castleaccount33 where AccountId=@AccountId) + @depositamount
update castleaccount33 set Balance=@bal,lastupdated=@tmpstp,ShowMessage=@ShowMessage where AccountId=@AccountId
insert into transaction33 values(@AccountId,@ShowMessage,@tmpstp,@depositamount )
set @TransactionId=@@identity
end
exec deposit 5560,'deposited',5000,null
alter proc withdrawl(@AccountId int,@ShowMessage varchar(50),@withdrawlamount int,@flag int out,@TransactionId int out) as
begin
declare @tmpstp datetime
set @tmpstp=CURRENT_TIMESTAMP
declare @bal int
if @withdrawlamount <=(select Balance from castleaccount33 where AccountId=@AccountId)
begin
set @bal =(select Balance from castleaccount33 where AccountId=@AccountId) - @withdrawlamount
update castleaccount33 set Balance=@bal,lastupdated=@tmpstp,ShowMessage=@ShowMessage where AccountId=@AccountId
insert into transaction33 values(@AccountId,@ShowMessage,@tmpstp,@withdrawlamount )
set @flag=1
set @TransactionId=@@identity
end
else
begin
set @flag=0
end
print @flag
print @TransactionId
end
exec withdrawl 5560,'withdrawl',100,null,null
create table transaction33 (TransactionId bigint primary key identity(7000,1),TransAccountId int foreign key references castleaccount33(AccountId)
,TransDescription varchar(50),Transdate datetime ,TransAmount bigint )
select * from transaction33
alter proc transfermoney(@accid1 int,@accid2 int,@amount int,@ShowMessage varchar(50),@transid int out,@flag int out) as
begin
declare @tmpstp datetime
set @tmpstp=CURRENT_TIMESTAMP
declare @bal int
declare @as1 varchar(30)
declare @as2 varchar(30)
SET @as1 = (SELECT m.AccountStatus
FROM castleaccount33 m
WHERE m.AccountId = @accid1)
SET @as2 = (SELECT m.AccountStatus
FROM castleaccount33 m
WHERE m.AccountId = @accid2)
--set @as1=(select AccountStatus from castleaccount33 where AccountId=@accid1)
--set @as2=(select AccountStatus from castleaccount33 where AccountId=@accid2)
if(@as1='Active' and @as2='Active')
begin
if(@accid1=@accid2)
begin
set @flag=0
end
else
begin
set @bal=(select Balance from castleaccount33 where AccountId=@accid1)
if(@bal>@amount and @bal>@bal-@amount )
begin
update castleaccount33 set Balance=Balance-@amount,lastupdated=@tmpstp,ShowMessage=@ShowMessage where AccountId=@accid1
insert into transaction33 values(@accid1,@ShowMessage,@tmpstp,@amount )
set @transid=@@IDENTITY
update castleaccount33 set Balance=Balance+@amount,lastupdated=@tmpstp,ShowMessage=@ShowMessage where AccountId=@accid2
insert into transaction33 values(@accid2,@ShowMessage,@tmpstp,@amount )
set @transid=@@IDENTITY
set @flag=1
end
else
begin
set @flag=2
end
end
end
else
begin
set @flag=3
end
print @flag
end
exec transfermoney 5563,5561,1440,'transfer',null,null
select * from transaction33
truncate table transaction33
select * from Custdet_1288
select * from castleaccount33
delete from castleaccount33 where AccountId=5560
truncate table transaction33