Parameterized Queries in SQL Server

 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:

sql
CREATE 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:

sql
EXEC 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:

csharp
using 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:

sql
DECLARE @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

1. Security: Parameterized queries help prevent SQL injection attacks by ensuring that user input is treated as data rather than executable code.
2. Performance: SQL Server can cache execution plans for parameterized queries, improving the performance of repeated query execution.
3. 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.