# Declare, View, Function, Stored Procedures in Microsoft SQL Server
## Declare
:::info
:bulb: **Định nghĩa**:
declare là khai báo 1 biến chứa giá trị đặc biệt. Mục đích để có thể dùng lại giá trị đó nhiều lần. Như ta thường sử dụng tên $$\pi$$ thay cho giá trị 3.14159265358979323846... của pi
:::
cú pháp cơ bản
```sql
Declare @<variable_name> <data_type> = <value> -- gán giá trị inline;
Set @<variable_name> = <value> or (subquery) -- gán giá trị gián tiếp
```
> variable_name: bắt đầu bằng '@'
> [data_type: click vào đây](https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15)
> gán giá trị inline dùng để gán cứng 1 giá trị đã biết trước. Trong trường hợp này phải gián giá trị có kiểu dữ liệu giống với khai báo hoặc convert được
vd thử chạy các câu sau đây
vd1 : khai báo đúng
```sql=
declare @a varchar(10) = 'hello'
declare @b varchar(10) = 3
declare @c date = '2021/12/21'
select @a, @b, @c
-- trong trường hợp hày giá trị số 3 của @b có thể chuyển sang dạng chuỗi.
-- giá trị chuỗi của @c đúng format có thể convert sang ngày
```
vd2 : khai báo sai
```sql=
declare @d date = '2021/21/12'
declare @e int = '3'
select @d, @e
-- giá trị chuỗi của @d không đúng format để có thể convert sang ngày
-- giá trị chuỗi '3 'của @e không thể chuyển sang dạng số.
```
> gán giá trị gián tiếp dùng để gán giá trị được lấy từ câu query
Xem các ví dụ bên dưới
```sql
DECLARE @max_rate MONEY
SET @max_rate =(
select MAX([Rate])
from [HumanResources].[EmployeePayHistory])
select @max_rate
```
khai báo và dùng lại trong câu truy vấn
```sql
DECLARE @find VARCHAR(30) = 'Man%';
/* Also allowed:
SET @find = 'Man%';
*/
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
```
<i class="fa fa-book fa-fw"></i> ngoài chứa các giá trị độc lập Declare có thể chứa giá trị cả table. Nhưng có các kĩ thuật khác tiện hơn để làm chuyện đó như With hay @view
## VIEW
:::info
:bulb: **Định nghĩa**:
là một bảng ảo trong cơ sở dữ liệu có nội dung được định nghĩa thông qua một câu lệnh SQL.
> _ Thông thường dữ liệu sẽ được lưu trong các bảng chứa trong bộ nhớ của data.
> _ View sẽ chỉ chứa câu lệnh SQL, khi nào ta dùng thì nó mới thực thi để lấy dữ liệu ra => ít tốn bộ nhớ nhưng hao performance
> _ Số cột tối đa của view là 1,024 columns.
:::
cú pháp
```sql=
CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name
AS
(SELECT... FROM... WHERE...) -- Không được dùng order by
```
ví dụ ta tạo 1 bảng lấy ra tên họ và ngày tuyển dụng của nhân viên
```sql=
CREATE VIEW [HumanResources].hiredate_view -- view này thuộc [HumanResources] schema
AS
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate
FROM HumanResources.Employee e
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;
GO
select *
from [HumanResources].hiredate_view
```
View có thể được update với các điều kiện nhất định được đưa ra dưới đây:
* Mệnh đề SELECT không được chứa từ khoá DISTINCT.
* Mệnh đề SELECT không được chứa các hàm tổng.
* Mệnh đề SELECT có thể không chứa các hàm tập hợp.
* Mệnh đề SELECT không được chứa các toán tử tập hợp.
* Mệnh đề SELECT không được chứa mệnh đề ORDER BY.
* Mệnh đề FROM không được chứa nhiều bảng.
* Mệnh đề WHERE không được chứa các truy vấn con.
* Truy vấn không chứa GROUP BY hoặc HAVING.
* Các cột được ứng lượng không thể được update.
* Tất cả các cột NOT NULL từ bảng cơ sở phải được select trong view để truy vấn INSERT hoạt động.
SỬA 1 dòng cho view
```sql=
UPDATE [HumanResources].hiredate_view
SET FirstName = 'A'
WHERE BusinessEntityID =285;
```
Ở đây ta chưa thêm được 1 dòng cho view vì các cột NOT NULL của 2 bảng ta chưa thêm hết vào view. Để thêm được 1 cột cho view ta thử tạo câu view sau
```sql
CREATE VIEW [Person].phone_person_777
AS
SELECT [BusinessEntityID], [PhoneNumber],[PhoneNumberTypeID],[ModifiedDate]
FROM [Person].[PersonPhone]
where [PhoneNumber] like '%777%'
--lấy kết quả ra thử
select *
from [Person].phone_person_777
-- thêm 1 cột cho view
INSERT INTO [Person].phone_person_777 VALUES(911, '999-999-9999', 1,GETDATE())
--hãy tự kiểm tra lại kết quả
```
# Function
:::info
:bulb: **Định nghĩa**:
Function trong T-SQL có thể trả về 1 giá trị (Scalar-value). Hoặc một table. Có thể dùng lại nhiều lần thường được sử dụng cho các mục đích
> Lưu lại câu query
> Lưu lại quy trình xử lý phức tạp (xử lý ngày tháng tính năm nhuận, xử lý chuỗi chuẩn hóa viết hoa viết thường,...)
:::
cú pháp cho Scalar Function
```sql=
CREATE FUNCTION [schema_name.]function_name (parameter_list)
RETURNS data_type AS
BEGIN
statements
RETURN value
END;
```
ví dụ
```sql
CREATE | ALTER FUNCTION fnSum (
@So1 int,
@So2 int
)
RETURNS int AS
begin
declare @result int;
set @result = @So1 + @So2
return @result
end;
```
chạy function
```sql
select [dbo].[fnSum](1,2)
```
Function trả về table:
```sql=
CREATE | ALTER FUNCTION [ schema_name. ] function_name (parameter_list)
RETURNS TABLE
[ WITH <clr_function_option> [ ,...n ] ]
[ ORDER ( <order_clause> ) ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
```
```sql=
CREATE FUNCTION fnFindPhoneNumber (
@number varchar(10)
)
RETURNS TABLE
AS
return
SELECT [BusinessEntityID], [PhoneNumber],[PhoneNumberTypeID],[ModifiedDate]
FROM [Person].[PersonPhone]
where [PhoneNumber] like '%'+@number+'%'
```
```sql=
select * from [dbo].[fnFindPhoneNumber]('777')
```
# Stored Procedures
:::info
:bulb: **Định nghĩa**:
Stored Procedures dùng để lưu trữ các câu query để có thể dùng lại nhiều lần. Cho phép truyền tham số vào để xử lý. Có thể thao tác các lệnh như Create, Update, Delete, Query, thậm chí là thực thi các câu Stored khác
:::
Cú pháp
```sql
CREATE PROCEDURE producer_name
@parameter1 data_type;
@parameter2 data_type;...-- các tham số truyển vào nếu có
AS
BEGIN
CÂU LỆNH LOGIC HOẶC QUERY
END
```
VÍ DỤ:
Tạo store tính tổng 2 số
```sql=
CREATE PROCEDURE spSum
@So1 int,
@So2 int
as
declare @Tong int;
set @Tong = @So1 + @So2;
return @Tong
```
chạy bằng cách ấn F5
Sau khi chạy thành công Store sẽ được lưu trong thư mục
nếu không thấy thì ra chọn chuột phải Refresh lại mục Stored Procedures
sau đó ta cùng thực thi store
```sql=
declare @Ket_qua int;
exec @Ket_qua = [dbo].[spSum] @So1 =2, @So2 =3
select @Ket_qua
```
ví dụ 2 tạo bảng bởi store
```sql=
CREATE PROCEDURE [Person].[spPhonePerson777]
AS
BEGIN
SELECT
[BusinessEntityID],
[PhoneNumber],
[PhoneNumberTypeID],
[ModifiedDate]
into [Person].[PhonePerson777]
FROM [Person].[PersonPhone]
where [PhoneNumber] like '%777%'
END
```
Bấm F5 để tạo stored và sau khi tạo ta kiểm tra trong mục Procedure như trên
Để thực thi store ta chạy lệnh
```sql=
exec [Person].[spPhonePerson777]
```
Sau khi chạy lệnh ta Refresh lại table xem đã có table tên [Person].[PhonePerson777] đã được tạo chưa?
### vấn đề
Trong ví dụ 2 ta chỉ thực thi Stored được 1 lần không thực thi lần 2 được. Vì bảng [Person].[PhonePerson777] đã được tạo nên khi chạy lần 2 bảng mới tạo sẽ bị trùng tên bảng cũ.
=> giải quyết trong store bằng cách xóa đi bảng cũ tạo thêm bảng mới
ta sửa lại câu query tạo stored như sau
```sql=
ALTER PROCEDURE [Person].[spPhonePerson777]
AS
BEGIN
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[PhonePerson777]')) DROP TABLE [Person].[PhonePerson777]
SELECT
[BusinessEntityID],
[PhoneNumber],
[PhoneNumberTypeID],
[ModifiedDate]
into [Person].[PhonePerson777]
FROM [Person].[PersonPhone]
where [PhoneNumber] like '%777%'
END
```
sau đó ta chạy lại sẽ chạy được nhiều lần vì ta đã xóa bảng trước đó
```sql
USE [AdventureWorks2017]
GO
/****** Object: StoredProcedure [Person].[spPhonePerson777] Script Date: 6/10/2021 2:12:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Person].[spPhonePerson777]
AS
BEGIN
SELECT
[BusinessEntityID],
[PhoneNumber],
[PhoneNumberTypeID],
[ModifiedDate]
into [Person].[PhonePerson777_temp]
FROM [Person].[PersonPhone]
where [PhoneNumber] like '%777%'
----------------------------------------------
if exists (select* from sys.objects where object_id = object_id(N'[Person].[PhonePerson777]'))
begin
print N'xuat hien table'
drop table [Person].[PhonePerson777];
print N'xoa table roi nha'
end
-------------------------------------------
else
begin
RAISERROR ('no found this table',10,1)
end
------------------------------------------
EXEC sp_rename '[Person].[PhonePerson777_temp]', 'PhonePerson777', 'object';
ALTER TABLE [Person].[PhonePerson777]
ADD PRIMARY KEY ([BusinessEntityID]);
END
```
ví dụ 3 viết store tìm ra Rate lương của một nhân viên dựa vào BusinessEntityID
```sql=
create procedure spFindRatePayment
@BusinessEntityID int,
@Result int out
as
begin
select @Result = max(Rate)
from HumanResources.EmployeePayHistory E
where E.BusinessEntityID = @BusinessEntityID
end
if @Result is null
begin
RAISERROR ('No record found',10,1)
end
else
print @Result
return
```
thực thi câu sau
```sql=
declare @a int;
declare @ID int = 1;
exec spFindRatePayment @ID, @Result = @a out;
```
thay đổi giá trị @ID =10000 và xem kết quả.
### pipeline tạo stored
#### pipline 1
```sql=
ALTER PROCEDURE [Person].[spPhonePerson777]
AS
BEGIN
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Person].[PhonePerson777_temp]'))
DROP TABLE [Person].[PhonePerson777_temp]
SELECT
[BusinessEntityID],
[PhoneNumber],
[PhoneNumberTypeID],
[ModifiedDate]
into [Person].[PhonePerson777_temp]
FROM [Person].[PersonPhone]
where [PhoneNumber] like '%777%'
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Person].[PhonePerson777]'))
DROP TABLE [Person].[PhonePerson777]
EXEC sp_rename '[Person].[PhonePerson777_temp]', 'PhonePerson777', 'object';
END
```
#### pipline 2
```sql=
ALTER PROCEDURE [Person].[spPhonePerson777]
AS
BEGIN
-- tạo bảng tạm
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Person].[PhonePerson777_temp]'))
DROP TABLE [Person].[PhonePerson777_temp]
SELECT
[BusinessEntityID],
[PhoneNumber],
[PhoneNumberTypeID],
[ModifiedDate]
into [Person].[PhonePerson777_temp]
FROM [Person].[PersonPhone]
where [PhoneNumber] like '%777%'
-- select từ bảng tạm k có trong bảng gốc
insert into [Person].[PhonePerson777]
select *
from [Person].[PhonePerson777_temp] temp
where temp.BusinessEntityID not in (select BusinessEntityID from [Person].[PhonePerson777])
-- xóa bảng tạm
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Person].[PhonePerson777_temp]'))
DROP TABLE [Person].PhonePerson777_temp
END
```
## Trả giá trị về trong stored procedure
_ Việc trả về giá trị trong STORED là optional, có cũng được không có cũng không sao
_ Stored Procedures có 2 cách trả về giá trị dùng **OUT/OUTPUT** parameter hoặc dùng câu lệnh **RETURN**. Ta cùng tìm hiểu 2 cách này
### RETURN
Với câu lệnh Return ta chỉ trả về được **1 kết quả kiểu INT**.
Thường chỉ dùng để thông báo việc thực thi STORED PROCEDURE có thành công hay không
Ta cùng kham khảo đoạn code sau
```sql=
CREATE procedure [Person].sp_insert_PhonePerson777
@BusinessEntityID INT,
@PhoneNumber INT,
@PhoneNumberTypeID INT
as
begin TRAN
declare @returnvalue int
insert into [Person].[PhonePerson777]
([BusinessEntityID],
[PhoneNumber],
[PhoneNumberTypeID],
[ModifiedDate])
values( @BusinessEntityID,
@PhoneNumber,
@PhoneNumberTypeID,
GETDATE())
set @returnvalue = @@ERROR
IF @returnvalue <> 0
BEGIN
PRINT @returnvalue
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
return @returnvalue
declare @x int
exec @x = [Person].sp_insert_PhonePerson777 13,12345,123
select @x 'Return_value'
```
### OUT/OUTPUT parameter
Có thể trả về **nhiều kết quả** với **nhiều kiểu dữ liệu**.
Dùng để
```sql=
create procedure Out_test3 (
@OutValue1 int output
,@OutValue2 datetime output
,@outValue3 varchar(10) output)
as
begin
set @OutValue1 = 10
set @OutValue2=GETDATE()
set @outValue3='test'
end
```
```sql=
declare @x int,@y datetime,@z varchar(10);
exec Out_test3 @OutValue1=@x output,@OutValue2=@y output ,@outValue3=@z output
select @x 'interger',@y 'datetime',@z 'varchar'
```
Sign in with Wallet
Connect another wallet