In this blog, we will discuss about sp_executesql stored procedure and what advantage sp_executesql stored procedure have it over EXEC.
sp_executesql is system stored procedure, it is used to execute the SQL script like EXEC but sp_executesql allows for SQL statement to be parameterized as instead of embedding or injecting it into SQL code so it stops any SQL Injection possibility.
There are some other difference between Exec and sp_executesql
sp_executesql
|
EXEC
|
Allow for statement to be parameterized
|
There is no option for parameterize column
|
No Risk of SQL Injection
|
High Risk of SQL Injection
|
Strongly typed parameters
|
No Strongly typed parameters
|
Performance Benefits - Cache an execution plane on first run
|
Create an execution plan for each run
|
Here is an example of sp_executesql:
CREATE PROCEDURE dbo.usp_validate_user
@UserName as varchar(10),
@Password as varchar(10)
AS
BEGIN
declare @sqlString as varchar(max)
SET @sqlString = 'Select id from dbo.Users Where UserName = @UserName and password = @Password'
EXECUTE sp_executesql @sqlString,
N'@UserName as varchar(10), @Password as varchar(10)'
@UserName, @Password
END
GO
