-
Notifications
You must be signed in to change notification settings - Fork 2
/
Phase3_Functions.sql
86 lines (78 loc) · 1.89 KB
/
Phase3_Functions.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
USE FlowerShop
Go
CREATE FUNCTION CheckPhone
(
-- Add the parameters for the function here
@HOME varchar(15)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @Result varchar(100);
Declare @LenH int;
if (@LenH<>11 or @HOME <> '0%')
begin
set @Result='Please Enter a Right Phone Number with its city code.';
end
ElSE
set @Result='True';
Return @Result
END
GO
CREATE FUNCTION CheckMobile
(
-- Add the parameters for the function here
@Mobile varchar(15)
)
RETURNS varchar(105)
AS
BEGIN
DECLARE @Result varchar(105);
Declare @LenM int;
set @LenM = LEN(@Mobile);
if (@LenM<>11 or @Mobile <> '09%' )
begin
set @Result='Please Enter a Right Mobile Number starts with 0';
end
ElSE
set @Result='True';
Return @Result
END
GO
select dbo.CheckPhone('33333333333')
select dbo.CheckMobile('09130759757')
CREATE FUNCTION Profit
(
-- Add the parameters for the function here
@today date
)
RETURNS int
AS
BEGIN
DECLARE @Result int;
DECLARE @kharid int;
DECLARE @Phoroosh int;
set @kharid= (select SUM(Total_peyment) from Buy group by Buy_date having Buy_date=@today)
Set @Phoroosh = (select SUM(Final_Cost) from [Order] group by Shop_date having Shop_date=@today)
set @Result=@Phoroosh-@kharid
Return @Result
END
GO
select * from Buy
select * from [Order]
CREATE FUNCTION Receipt
(
-- Add the parameters for the function here
@OrderID int
)
RETURNS table
AS
return(
select [Order].ID, Customer_ID,Order_type ,Flower_ID ,Flower.Flower_name,Colour.color_name,FlowersInOrder.Number ,FlowersInOrder.Price
,Shop_date, Occasion_ID, Package_ID,Packaging.Packag_Type,Packaging.Design,Packaging.[Card], Wrapping_price, Total_Cost, Discount, Final_Cost, More_info
from [Order],FlowersInOrder,Flower,Colour,Packaging
where [Order].ID=FlowersInOrder.Order_ID and FlowersInOrder.Flower_ID=Flower.ID and
Flower.Flower_color_ID=Colour.color_ID and [Order].Package_ID=Packaging.ID
and [Order].ID=@OrderID
)
GO