SQL_DEV/EDUCATION/function_and_procedure.sql at master · Brezencat/SQL_DEV · GitHub

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


drop function if exists dbo.f_HELLO

CREATE FUNCTION dbo.f_HELLO ()
RETURNS varchar(30)
AS
BEGIN
	DECLARE @text varchar(30) = 'Ìàðñ';

	RETURN @text;
END;

GO

select dbo.f_HELLO()
go


declare @MY_TEXT varchar(30) --= dbo.f_HELLO()
select  @MY_TEXT = dbo.f_HELLO()
print @MY_TEXT


CREATE FUNCTION dbo.f_ONLY_DATE (@Date datetime)
RETURNS date
AS
BEGIN
	RETURN CAST(@Date as date);
END;

GO

select dbo.f_ONLY_DATE (getdate()), getdate()

CREATE FUNCTION dbo.f_EOM (@Date datetime)
RETURNS date
AS
BEGIN
	RETURN EOMONTH(@Date);
END;

GO


select dbo.f_EOM (getdate())


CREATE FUNCTION dbo.f_CUSTOMER()
RETURNS TABLE
AS
RETURN
(
	SELECT	customer_id
		,	customer
		,	dt_created
	FROM dbo.customer
);
GO

select *
from dbo.f_CUSTOMER()
where customer = dbo.f_HELLO()


CREATE FUNCTION dbo.f_CUSTOMER_FILTR (@customer varchar(128))
RETURNS TABLE
AS
RETURN
(
	SELECT	customer_id
		,	customer
		,	dt_created
	FROM dbo.customer
	WHERE customer = @customer
);
GO

select *
from dbo.f_CUSTOMER_FILTR ('Ìàðñ')


CREATE FUNCTION dbo.F_CONTRAGENT_FILTR (@customer varchar(128), @date datetime)
RETURNS TABLE
AS
RETURN
(
	SELECT	c.contragent
		,	cus.customer
		,	cus.dt_created as dt_cus
		,	c.dt_created as dt_contr
	FROM dbo.customer as cus
	inner join  dbo.contragent as c
		on c.customer_id = cus.customer_id
	WHERE customer = @customer
		and cus.dt_created > @date
);
GO

select *
from dbo.F_CONTRAGENT_FILTR (dbo.f_HELLO(), '20100101')


GO

CREATE PROC dbo.SELECT_CONTRAGENT_FILTR
		@customer varchar(128)
	,	@date datetime

AS
BEGIN
	SELECT	c.contragent
		,	cus.customer
		,	cus.dt_created as dt_cus
		,	c.dt_created as dt_contr
	FROM dbo.customer as cus
	inner join  dbo.contragent as c
		on c.customer_id = cus.customer_id
	WHERE customer = @customer
		and cus.dt_created > @date;
END


exec dbo.SELECT_CONTRAGENT_FILTR
	@customer = 'Ìàðñ'
	,@date = '20100101'

select contragent
into #TEMP
from dbo.F_CONTRAGENT_FILTR ('Ìàðñ', '20100101')

--drop table if exists #TEMP