In SQL Server, parameterized queries can be used in various ways, including through stored procedures, prepared statements, and the use of parameters in direct SQL commands. Here are some examples demonstrating different methods for creating parameterized queries in SQL Server.
Using Stored Procedures
Stored procedures are a common way to use parameterized queries in SQL Server. Here's an example:
Creating a Stored Procedure
First, create a stored procedure in SQL Server:
sqlCREATE PROCEDURE InsertUser
@Name NVARCHAR(50),
@Age INT
AS
BEGIN
INSERT INTO Users (Name, Age)
VALUES (@Name, @Age)
END
Executing the Stored Procedure
You can execute the stored procedure from SQL Server Management Studio (SSMS) or from an application using a parameterized query.
From SSMS:
sqlEXEC InsertUser @Name = 'Alice', @Age = 30;
EXEC InsertUser @Name = 'Bob', @Age = 25;
Using ADO.NET in C#
Here is an example of using parameterized queries with ADO.NET in a C# application:
csharpusing System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "your_connection_string_here";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// Insert user
using (SqlCommand cmd = new SqlCommand("INSERT INTO Users (Name, Age) VALUES (@Name, @Age)", conn))
{
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = "Alice";
cmd.Parameters.Add("@Age", SqlDbType.Int).Value = 30;
cmd.ExecuteNonQuery();
}
// Select users
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE Age > @Age", conn))
{
cmd.Parameters.Add("@Age", SqlDbType.Int).Value = 20;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"ID: {reader["ID"]}, Name: {reader["Name"]}, Age: {reader["Age"]}");
}
}
}
}
}
}
Using SQL Server Management Studio (SSMS)
You can also use parameterized queries directly in SSMS by declaring and setting variables:
sqlDECLARE @Name NVARCHAR(50);
DECLARE @Age INT;
SET @Name = 'Alice';
SET @Age = 30;
INSERT INTO Users (Name, Age)
VALUES (@Name, @Age);
SET @Name = 'Bob';
SET @Age = 25;
INSERT INTO Users (Name, Age)
VALUES (@Name, @Age);
DECLARE @AgeThreshold INT;
SET @AgeThreshold = 20;
SELECT * FROM Users
WHERE Age > @AgeThreshold;
Benefits of Using Parameterized Queries in SQL Server
- Security: Parameterized queries help prevent SQL injection attacks by ensuring that user input is treated as data rather than executable code.
- Performance: SQL Server can cache execution plans for parameterized queries, improving the performance of repeated query execution.
- Readability and Maintainability: Separating query logic from data makes the code easier to read and maintain.
Using parameterized queries in SQL Server, whether through stored procedures, application code, or directly in SSMS, is a best practice that enhances the security and performance of your database operations.
