Bài viết này sẽ hướng dẫn cách sử dụng quy trình lưu trữ hệ thống sp_executesql để chạy các truy vấn SQL tĩnh và động.

Bạn đang xem : sql thực thi sp với các tham số

Bài viết này giải thích cách sử dụng quy trình lưu trữ hệ thống sp_executesql để chạy các truy vấn SQL tĩnh và động và
cách truyền các tham số đầu vào và đầu ra trong khi sử dụng nó.

  • Lưu ý: Tất cả các ví dụ trong bài viết này đều được thực thi trong SQL Server 2019 bằng cơ sở dữ liệu AdventureWorks2017

Thủ tục lưu trữ sp_executesql là gì?

Thủ tục được lưu trữ trong SQL Server được sử dụng để chạy một hoặc nhiều câu lệnh SQL được lưu trữ trong một chuỗi. Cái này
thủ tục được lưu trữ cho phép thực thi các chuỗi tĩnh hoặc các chuỗi được xây dựng động.

Cú pháp

Cú pháp của thủ tục được lưu trữ này rất đơn giản; bạn cần chuyển câu lệnh SQL dưới dạng chuỗi Unicode hoặc
theo sau là các tham số nếu chúng tồn tại.

1

sp_executesql

N

‘statment’

,

< p class = "crayon-h">

[

{

thông số

định nghĩa

}

,

{

tham số

giá trị

}

]

Thực thi truy vấn SQL tĩnh

Như đã đề cập ở trên, sp_executesql có thể được sử dụng để thực thi một câu lệnh SQL tĩnh. Câu lệnh này có thể được chuyển trực tiếp dưới dạng một chuỗi Unicode tới thủ tục được lưu trữ hoặc được lưu trữ trong một biến kiểu NVARCHAR.

Ví dụ, chúng ta hãy giả sử rằng trong cơ sở dữ liệu AdventureWorks, hãy giả sử rằng chúng ta có một truy vấn trả về tất cả
nhân viên từ bảng Person như sau:

1

2

3

4

5

6

7

CHỌN

[

BusinessEntityID

]

, < / p>

[

Tiêu đề

]

,

[

FirstName

]

< / p>

,

[

MiddleName

]

,

[

LastName

< p class = "crayon-sy">]

TỪ

[

AdventureWorks2017

]

.

[

Người

]

.

[

Người

]

WHERE

[

PersonType

]

=

‘EM’

Hình 1 – Kết quả truy vấn đầu tiên

Truy vấn này cũng có thể được thực hiện như sau:

1

2

3

4

5

6

7

THỰC HIỆN

sp_executesql

N

‘CHỌN [BusinessEntityID]

, [Title]

, [FirstName ]

, [MiddleName]

, [LastName]

< p class = "crayon-line" id = "urvanov-cú pháp-highlighter-628c6e40e5a36509246253-6">

TỪ [AdventureWorks2017]. [Person]. [Person]

WHERE [PersonType] = ‘

‘ EM ‘

‘ ‘

Hình 2 – Kết quả truy vấn thứ hai

  • Lưu ý rằng ký tự N trước lệnh SQL là để chuyển đổi chuỗi thành chuỗi Unicode

Một phương pháp khác để chạy truy vấn này là lưu trữ nó trong một biến kiểu NVARCHAR và chuyển nó dưới dạng tham số:

1

2

3

4

5

6

7

8

KHAI BÁO

@

SQL

NVARCHAR

(

4000

)

=

< p class = "crayon-i"> N

‘CHỌN [BusinessEntityID]

, [Tiêu đề]

, [FirstName]

, [MiddleName]

, [LastName]

TỪ [AdventureWorks2017]. [Người]. [Người]

WHERE [PersonType] = ‘

‘EM’

;

THỰC HIỆN

sp_executesql

@

SQL

Hình 3 – Kết quả truy vấn thứ ba

Như được trình bày trong phần này, khi thực hiện một truy vấn SQL tĩnh, không có lợi ích gì khi sử dụng sp_executesql được lưu trữ
thủ tục.

Thực thi truy vấn SQL động

Mục đích chính của việc sử dụng thủ tục được lưu trữ sp_executesql là chạy các truy vấn SQL động. Truy vấn SQL động là những truy vấn được xây dựng trong thời gian chạy dựa trên một hoặc nhiều giá trị biến.

Ví dụ: giả sử rằng chúng ta cần tạo một truy vấn trả về tất cả các nhân viên bằng cách hiển thị các cột có
Kiểu dữ liệu NVARCHAR. Để lấy danh sách các cột NVARCHAR trong bảng Người, chúng ta có thể sử dụng truy vấn sau:

1

2

CHỌN

COLUMN_NAME

TỪ

INFORMATION_SCHEMA

.

CỘT

WHERE

DATA_TYPE

=

‘NVARCHAR’

TABLE_SCHEMA

=

‘Person’

TABLE_NAME

=

‘Người’

;

Để chỉ chọn các cột NVARCHAR từ bảng Person, trước tiên chúng ta nên nối các tên cột được trả về bởi
truy vấn ở trên (chúng ta có thể sử dụng hàm STRING_AGG ()). Sau đó, chúng ta nên xây dựng một câu lệnh SQL và thực thi nó như
sau:

1

2

3

4

5

6

7

8

9

KHAI BÁO

@

cols

NVARCHAR

(

4000

)

=

< p class = "crayon-s"> ”

;

KHAI BÁO

@

SQL

NVARCHAR

(

4000

)

=

< p class = "crayon-sy">;

CHỌN

@

cols

=

STRING_AGG

(

COLUMN_NAME

,

‘,’

)

TỪ

INFORMATION_SCHEMA

.

CỘT

WHERE

Xem Thêm  So sánh JavaScript và toán tử logic - javascript bằng hoặc nhỏ hơn

< p class = "crayon-h">

DATA_TYPE

=

‘NVARCHAR’

TABLE_ SCHEMA

=

‘Person’

TABLE_NAME

=

‘Person’

;

CHỌN

@

SQL

=

‘CHỌN’

+

@

cols

+

‘TỪ Người .Person WHERE Per sonType = ‘

‘ EM ‘

‘ ‘

;

THỰC HIỆN

sp_executesql < / p>

@

SQL

;

Hình 4 – Ví dụ về truy vấn SQL động

  • Lưu ý: Sử dụng truy vấn SQL động không phải là một phương pháp hay khi các biến được sử dụng để chỉ lưu trữ các giá trị lọc của mệnh đề WHERE

Một ví dụ khác mà chúng ta cần sử dụng truy vấn SQL động là nếu chúng ta cần truy vấn tất cả các bảng được tạo trên lược đồ Person:

1

CHỌN

TABLE_NAME

TỪ

INFORMATION_SCHEMA

.

BẢNG BIỂU

WHERE

TABLE_SCHEMA

=

‘Person’

TABLE_TYPE

=

‘BẢNG CƠ SỞ’

Giả sử chúng ta có thể tạo các truy vấn chọn trả về hàng đầu tiên của mỗi bảng bằng cách sử dụng SQL sau
yêu cầu:

1

CHỌN

‘CHỌN HÀNG ĐẦU 1 * TỪ NGƯỜI.’

+

TABLE_NAME

TỪ

INFORMATION_SCHEMA

.

BẢNG BIỂU

ĐÂU

TABLE_SCHEMA

=

‘Person’

< / p>

TABLE_TYPE

=

‘BẢNG CƠ SỞ’

Hình 5 – Tạo truy vấn bằng lệnh SQL

Để thực hiện các truy vấn đó, chúng ta phải nối chúng vào một câu lệnh SQL và chuyển chúng dưới dạng tham số cho
thủ tục lưu trữ sp_executesql.

1

2

3

4

5

6

7

KHAI BÁO

@

SQL

NVARCHAR

(

MAX

)

;

CHỌN

@

SQL

=

STRING_AGG

(

‘CHỌN TOP 1 * TỪ NGƯỜI.’

+

TABLE_NAME

,

‘;’

)

TỪ

INFORMATION_SCHEMA

.

BẢNG BIỂU

WHERE

TABLE_SCHEMA

=

‘Person’

TABLE_TYPE

=

‘BẢNG CƠ SỞ’

;

< / p>

THỰC HIỆN

sp_executesql

< / p>

@

SQL

Hình 6 – Thực thi hàng loạt lệnh SQL

Làm việc với các tham số

Như đã đề cập trong phần đầu tiên, để thực hiện một truy vấn được tham số hóa, chúng ta nên chuyển hai tham số cho thủ tục được lưu trữ; đầu tiên phải chứa tất cả các tên tham số và kiểu dữ liệu (định nghĩa). Cái thứ hai phải chứa tất cả các giá trị. Phần này cung cấp hai ví dụ: truyền các giá trị đầu vào cho câu lệnh SQL được thực thi và lưu trữ một giá trị đầu ra vào một tham số đầu ra.

Truyền một tham số đầu vào

Giả sử rằng chúng ta cần sử dụng lại truy vấn chỉ chọn các cột NVARCHAR từ bảng Person:

1

2

3

4

5

6

7

8

9

KHAI BÁO

@

cols

NVARCHAR

(

4000

)

=

< p class = "crayon-s"> ”

;

KHAI BÁO

@

SQL

NVARCHAR

(

4000

)

=

< p class = "crayon-sy">;

CHỌN

@

cols

=

STRING_AGG

(

COLUMN_NAME

,

‘,’

)

TỪ

INFORMATION_SCHEMA

.

CỘT

WHERE

< p class = "crayon-h">

DATA_TYPE

=

‘NVARCHAR’

TABLE_ SCHEMA

=

‘Person’

TABLE_NAME

=

‘Person’

;

CHỌN

@

SQL

=

‘CHỌN’

+

@

cols

+

‘TỪ Người .Person WHERE Per sonType = ‘

‘ EM ‘

‘ ‘

;

THỰC HIỆN

sp_executesql < / p>

@

SQL

;

Nhưng lần này, chúng ta cần lọc các giá trị đó cho Họ và Tên được lọc dựa trên hai tham số:
@Tên và họ.

1

2

3

4

5

6

7

8

9

10

11

KHAI BÁO

@

cols

NVARCHAR

(

4000

)

=

;

KHAI BÁO

@

< p class = "crayon-k"> SQL

NVARCHAR

(

4000

)

=

;

DECLARE

@

params

NVARCHAR

(

4000

)

=

‘@ Họ NVARCHAR (255), @FirstName NVARCHAR (255)’

CHỌN

< p class = "crayon-h">

@

cols

=

STRING_AGG

(

COLUMN_NAME

,

‘,’

)

TỪ

INFORMATION_SCHEMA

.

CỘT

WHERE

DATA_TYPE

=

‘NVARCHAR’

TABLE_SCHEMA

=

‘Person’

TABLE_NAME

=

‘Person’

;

CHỌN

< p class = "crayon-sy"> @

SQL

=

‘CHỌN ‘

+

@

cols

+

‘TỪ Person.Person WHERE PersonType =’

‘EM’

AND LastName = @LastName AND FirstName = @FirstName ‘

;

THỰC HIỆN

sp_executesql

@

SQL

,

@

params

, < / p>

@

FirstName

=

‘Jossef’

,

@

Họ

=

‘Goldberg’

;

Trong hai dòng đầu tiên, chúng tôi đã xác định các biến được sử dụng để xây dựng câu lệnh SQL động như đã giải thích trước đó. Trong dòng thứ ba, chúng tôi đã tạo một biến lưu trữ định nghĩa của các tham số.

Khi thực hiện thủ tục được lưu trữ, chúng tôi chuyển biến lưu trữ định nghĩa tham số SQL động theo sau
bởi các giá trị của các tham số đó.

Hình 7 – Truyền các tham số đầu vào cho một truy vấn SQL động

Lưu trữ giá trị vào tham số đầu ra

Bây giờ, giả sử rằng chúng ta cần một lệnh SQL trong đó người dùng chuyển tên bảng làm đầu vào và nhận giá trị đếm hàng vào tham số đầu ra.

Xem Thêm  Cách tạo kiểu bảng bằng CSS - hướng dẫn kiểu bảng css

Sự khác biệt chính với các tham số đầu vào là lần này chúng ta cần thêm từ khóa OUTPUT trong tham số
định nghĩa và chúng ta nên xác định một biến bên ngoài SQL động để lưu trữ giá trị đầu ra như sau:

1

2

3

4

5

6

7

8

KHAI BÁO

@

Tên bảng

NVARCHAR

(

255

)

=

< p class = "crayon-s"> ‘Person.Person’

;

KHAI BÁO

@

outCount

LỚN

;

KHAI BÁO

@

params

< p class = "crayon-h">

NVARCHAR

(

255

)

< p class = "crayon-h">

=

‘@ Đếm ĐẦU RA LỚN’

KHAI BÁO

@

SQL

NVARCHAR

(

4000

)

=

‘SELECT @Count = COUNT (*) FROM’

+

@

Tên bảng

;

THỰC HIỆN

sp_executeSQL

@

SQL

,

@

params

,

@

Đếm

=

@

outCount

ĐẦU RA

;

IN

(

@

outCount

)

Trong truy vấn này, biến @outCount được xác định bên ngoài câu lệnh SQL động được sử dụng để đọc giá trị được lưu trữ
trong biến @Count được xác định trong câu lệnh SQL động.

Hình 8 – Sử dụng tham số đầu ra trong truy vấn SQL động

Exec so với sp_executesql

Sự khác biệt chính giữa các toán tử EXEC hoặc EXECUTE và thủ tục lưu trữ tích hợp sẵn sp_executesql là toán tử EXEC được sử dụng để thực thi một thủ tục được lưu trữ hoặc một lệnh SQL được truyền dưới dạng một chuỗi hoặc được lưu trữ trong một biến. Đồng thời, nó không thể thực hiện các truy vấn được tham số hóa, có nghĩa là nó dễ bị tấn công hơn đối với SQL injection.

Kết luận

Bài viết này giải thích ngắn gọn thủ tục lưu trữ tích hợp sẵn sp_executesql trong SQL Server và cách sử dụng nó để thực thi các truy vấn SQL động. Ngoài ra, chúng tôi đã học cách làm việc với các tham số đầu vào và đầu ra.


Xem thêm những thông tin liên quan đến chủ đề sql thực thi sp với các tham số

exec vs sp executesql in sql server

  • Tác giả: kudvenkat
  • Ngày đăng: 2017-04-18
  • Đánh giá: 4 ⭐ ( 8892 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Text version of the video
    http://csharp-video-tutorials.blogspot.com/2017/04/exec-vs-spexecutesql-in-sql-server.html

    Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
    https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

    Slides
    http://csharp-video-tutorials.blogspot.com/2017/04/exec-vs-spexecutesql-in-sql-server_18.html

    Dot Net & SQL Server Tutorials
    https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

    In this video we will discuss the difference between exec and sp_executesql.

    In SQL Servere we have 2 options to execute dynamic sql
    1. Exec/Execute
    2. sp_executesql

    We discussed sp_executesql in detail in Part 138 of SQL Server tutorial. Please check out that video if you are new to sp_executesql.

    If you do a quick search on the internet for the difference between exec and sp_executesql, you will see that many articles on the web states using exec over sp_executesql will have the following 2 problems
    1. It open doors for sql injection attacks
    2. Cached query plans may not be reused and leads to poor performance

    This is generally true, but if you use QUOTENAME() function you can avoid sql injection attacks and with sql server auto-parameterisation capability the cached query plans can be reused so performance is also not an issue. Let’s understand these with examples.

    What is exec() in SQL Server
    Exec() or Execute() function is used to execute dynamic sql and has only one parameter i.e the dynamic sql statement you want to execute.

    As you can see in the example below, we are concatenating strings to build dynamic sql statements which open doors for sql injection.

    Declare @FN nvarchar(50)
    Set @FN = ‘John’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ”’ + @FN + ””
    Exec(@sql)

    If we set @FN parameter to something like below, it drops SalesDB database

    Declare @FN nvarchar(50)
    Set @FN = ”’ Drop Database SalesDB –”’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ”’ + @FN + ””
    Exec(@sql)

    However, we can prevent SQL injection using the QUOTENAME() function as shown below.

    Declare @FN nvarchar(50)
    Set @FN = ”’ Drop Database SalesDB –”’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ‘ + QUOTENAME(@FN,””)
    –Print @sql
    Exec(@sql)

    Notice with the quotename function we are using a single quote as a delimiter. With the use of this function if there is a single quote in the user input it is doubled.

    For example, if we set @FN=’John’, notice the string ‘John’ is wrapped in single quotes

    Declare @FN nvarchar(50)
    Set @FN = ‘John’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ‘ + QUOTENAME(@FN,””)
    Print @sql

    When the above query is executed the following is the query printed
    Select * from Employees where FirstName = ‘John’

    Along the same lines, if we try to inject sql, QUOTENAME() function wraps all that input in another pair of single quotes treating it as a value for the FirstName column and prevents SQL injection.

    With sql server auto-parameterisation capability the cached query plans can be reused. SQL Server can detect parameter values and create parameterised queries on its own, even if you don’t explicitly declare them. However, there are exceptions to this. Auto-parameterisation comes in 2 flavours – Simple and Forced. We will discuss auto-parameterisation in detail in a later video.

    Execute the following DBCC command to remove all entries from the plan cache
    DBCC FREEPROCCACHE

    Execute the following query. Notice we have set @FN=’Mary’
    Declare @FN nvarchar(50)
    Set @FN = ‘Mary’
    Declare @sql nvarchar(max)
    Set @sql = ‘Select * from Employees where FirstName = ‘ + QUOTENAME(@FN,””)
    Exec(@sql)

    Execute the following query to retrieve what we have in the query plan cache
    SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
    ORDER BY cp.usecounts DESC

    Notice we have an auto-parameterised query and at the moment usecounts is 1.

    Now change @FN=’Mark’ and execute the same query. After the query is completed, retrieve the entries from the plan cache. Notice the usecounts for the auto-parameterised query is 2, suggesting that the same query plan is reused.

    Summary
    1. If you use QUOTENAME() function, you can prevent sql injection while using Exec()
    2. Cached query plan reusability is also not an issue while using Exec(), as SQL server automatically parameterize queries.
    3. I personally prefer using sp_executesql over exec() as we can explicitly parameterise queries instead of relying on sql server auto-parameterisation feature or QUOTENAME() function. I use Exec() only in throw away scripts rather than in production code.

Stored Procedure Sql Là Gì, Stored Procedure Trong Sql Server

  • Tác giả: hungthinhreals.com
  • Đánh giá: 4 ⭐ ( 4924 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

Giới thiệu SQL động. Tìm kiếm và phân trang với sp_ExecuteSql

  • Tác giả: viblo.asia
  • Đánh giá: 5 ⭐ ( 7809 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: 1. Giới thiệu SQL động Khi bạn cần viết một thủ tục, trong đó tùy thuộc vào giá trị của các tham số đầu vào mà câu lệnh SQL cần thực hiện sẽ thay đổi, bạn cần tạo lập chuỗi lệnh SQL trong chương trình…

Bài 6: Stored Procedure và T-SQL nâng cao

  • Tác giả: timoday.edu.vn
  • Đánh giá: 5 ⭐ ( 1204 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Tìm hiểu cách import và export dữ liệu vào trong hệ quản trị cơ sở dữ liệu SQL Server. Ngoài ra nghiên cứu Stored Procedure và Cursor trong SQL Server

Stored Procedure trong SQL Server 2014 (Bài 10)

  • Tác giả: giasutinhoc.vn
  • Đánh giá: 5 ⭐ ( 5022 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

SQL Server: Thủ tục lưu trữ (SP)

  • Tác giả: www.v1study.com
  • Đánh giá: 3 ⭐ ( 2335 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Thủ tục lưu trữ (Stored Procedure – SP) là một nhóm các lệnh Transact-SQL (T-SQL) đóng vai trò như một khối lệnh đơn dùng để thực hiện một công việc (tác

Tham số OUTPUT của Stored Procedure trong SQL Server

  • Tác giả: freetuts.net
  • Đánh giá: 5 ⭐ ( 3997 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Hướng dẫn sư dụng Tham số OUTPUT của Stored Procedure trong SQL Server, cách dùng lệnh OUTPUT trong SQL server để khai báo biến trả về của procedure

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình

Xem Thêm  Cách tắt tính năng Tự động điền và Tự động điền của Trình duyệt trên các Trường Nhập và Biểu mẫu HTML - cách tắt đề xuất tự động trong html hộp văn bản

By ads_php