Dynamic statement sqlserver

 EXEC sp_executesql @DynamicSQL, N'@FirstName NVARCHAR(50), @LastName NVARCHAR(50)', @FirstName, @LastName

In the EXEC sp_executesql statement, the subsequent parameters need to be passed in a specific order. The order of these parameters is as follows:

1. The first parameter is the dynamic SQL statement itself, usually a variable of type NVARCHAR (MAX), containing the SQL query to be executed.

2. The second parameter is a list that defines the parameters contained in the dynamic SQL statement. This is an NVARCHAR(MAX) type string, usually given in the form of N’@parameter 1 data type, @parameter 2 data type, …’, where parameter 1, parameter 2, etc. are referenced in dynamic SQL statements The names of the parameters, and the data type is the data type of these parameters.

3. The next parameters are the actual parameter values corresponding to the above parameter list, passed in the same order as in the parameter list.

Here are a few examples from easy to difficult:

Level1: Simple filtering

Level1: Simple filtering

Suppose there is a table named “Employees” that contains employee information, including the “FirstName” and “LastName” fields. You can use dynamic SQL to build a query that filters employee records based on user-entered criteria:

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @FirstName NVARCHAR(50)
DECLARE @LastName NVARCHAR(50)

--Set search criteria provided by the user
SET @FirstName = 'John'
SET @LastName = 'Doe'

-- Build dynamic SQL statements
SET @DynamicSQL = 'SELECT * FROM Employees WHERE 1=1' -- 1=1 is used to ensure that there is always one condition so that other conditions can be added later

--Add conditions based on user input
IF @FirstName IS NOT NULL


    SET @DynamicSQL = @DynamicSQL + ' AND FirstName = @FirstName' --Note: Add spaces to the condition

IF @LastName IS NOT NULL
    SET @DynamicSQL = @DynamicSQL + ' AND LastName = @LastName'

--Execute dynamic SQL statements
EXEC sp_executesql @DynamicSQL, N'@FirstName NVARCHAR(50), @LastName NVARCHAR(50)', @FirstName, @LastName

We first declare a variable @DynamicSQL which will be used to build dynamic SQL statements. We then build a SQL query based on the user-supplied criteria (FirstName and LastName). Finally, we executed the dynamic SQL statement using the sp_executesql stored procedure, passing the user-supplied parameters.

’ AND FirstName = @FirstName’ –Note: Add a space before the condition AND add a space before it! ! ! ! ! ! !

Level 2: + order by

Level 2: + order by
Suppose you have a table called “Products” that contains information about products, including the “ProductName” and “Price” fields. You can use dynamic SQL to sort products based on user-selected sorting criteria.

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @SortColumn NVARCHAR(50)
DECLARE @SortDirection NVARCHAR(4)

--Set user-provided sorting conditions
SET @SortColumn = 'ProductName'
SET @SortDirection = 'ASC' -- ASC means ascending order, DESC means descending order

-- Build dynamic SQL statements
SET @DynamicSQL = 'SELECT * FROM Products'

-- Add dynamic ORDER BY clause
SET @DynamicSQL = @DynamicSQL + ' ORDER BY ' + @SortColumn + ' ' + @SortDirection --Note there are spaces here

--Execute dynamic SQL statements
EXEC sp_executesql @DynamicSQL

SET @DynamicSQL = @DynamicSQL + ’ ORDER BY ’ + @SortColumn + ’ ’ + @SortDirection –Note that there are spaces ’ ’ and the parameters have plus signs! ! ! ! !

Level 2.5 parameterized query

Level 2.5 Parameterized Query

Q: Why are some parameters directly embedded in the query, while others need to use the plus sign + to build a dynamic SQL query?
A: The main reason is that the data types are different.

1.Parameter value is text (character data): If your parameter value is text, such as city name or customer name, you can embed it directly in the SQL statement without using the plus sign + connection . For example:
```
DECLARE @City NVARCHAR(50)
SET @City = 'New York'

-- Directly embed text parameters
SELECT CustomerName, PhoneNumber FROM Customers WHERE City = @City
```
In this case, text parameters can be placed directly in the query statement because they are compatible with SQL syntax.

2.Parameter value is an identifier (column name, etc.): If your parameter value is an identifier, such as a column name, you need to use dynamic SQL to build the query, because directly embedding identifiers is not allowed of. In this case, you need to use the plus sign + to dynamically build the query string to ensure that the identifier is quoted correctly. For example:
```
DECLARE @ColumnName NVARCHAR(50)
SET @ColumnName = 'CustomerName'

-- Build dynamic SQL queries, referencing column names
DECLARE @SqlStatement NVARCHAR(MAX)
SET @SqlStatement = 'SELECT ' + QUOTENAME(@ColumnName) + ' FROM Customers'

-- Execute dynamic SQL queries
EXEC sp_executesql @SqlStatement
```
This is because placing column names directly in SQL statements is not allowed, and you need to use specific functions (such as QUOTENAME) to ensure that column names are quoted correctly and avoid syntax errors.

In summary, how you build a parameterized query depends on the data type and purpose of the parameters. Character parameters can be directly embedded in the query, while identifier parameters require dynamic SQL to be used to build the query.

Level 3: order by + insert first

Level 3: order by + insert first
Suppose you have a table named “SourceTable” that contains the data you want to sort and insert, and a target table named “TableA” into which you want to insert the sorted data.

DECLARE @DynamicSQL NVARCHAR(MAX)

-- Build dynamic SQL queries to sort data
SET @DynamicSQL = 'SELECT * FROM SourceTable ORDER BY ColumnToOrderBy'

--Execute dynamic SQL query and insert into TableA
INSERT INTO TableA
EXEC sp_executesql @DynamicSQL

Level 4: Generate the first 20 data

Level 4: Generate the first 20 data

Suppose you have a table named “SourceTable” that contains the data you want to sort and insert, and a target table named “TableA” into which you want to insert the first 20 rows of data after sorting.

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @SortColumn NVARCHAR(50)

-- Set the user-provided sort column
SET @SortColumn = 'ColumnNameToSortBy'

--Build a dynamic SQL query to sort the data and select the top 20 rows
SET @DynamicSQL = '
    INSERT INTO TableA (Column1, Column2, Column3) -- List the columns of the target table to be inserted
    SELECT Column1, Column2, Column3 -- List the columns to select
    FROM (
        SELECT Column1, Column2, Column3, ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ') AS RowNum
        FROM SourceTable
    ) AS RankedData
    WHERE RowNum <= 20'

-- Execute dynamic SQL queries
EXEC sp_executesql @DynamicSQL

We first declare a variable @DynamicSQL and then build a dynamic SQL query. This query uses the ROW_NUMBER() window function to sort the data in the “SourceTable” table, in order of the “YourColumn” column, and assign a row number to each row. Then, we select the first 20 rows of data and insert them into the “TableA” table.

Level 5: Parameters in stored procedures are variables

Level 5: Parameters in stored procedures are variables
1. It can be used directly, but it is not recommended.

CREATE PROCEDURE MyDynamicSQLProcedure
    @SortColumn NVARCHAR(50),
    @TableAName NVARCHAR(50)
AS
BEGIN
    DECLARE @DynamicSQL NVARCHAR(MAX)

    --Build a dynamic SQL query to sort the data and select the top 20 rows
    SET @DynamicSQL = '
        INSERT INTO ' + @TableAName + ' (Column1, Column2, Column3) -- List the columns of the target table to be inserted
        SELECT Column1, Column2, Column3 -- List the columns to select
        FROM (
            SELECT Column1, Column2, Column3, ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ') AS RowNum
            FROM SourceTable
        ) AS RankedData
        WHERE RowNum <= 20'

    -- Execute dynamic SQL queries
    EXEC sp_executesql @DynamicSQL
END

The stored procedure “MyDynamicSQLProcedure” defines two parameters: @SortColumn and @TableAName. These parameters are used inside the stored procedure to build dynamic SQL queries without the need to repeatedly define the data type of the parameters in sp_executesql, (for example, EXEC MyDynamicSQLProcedure ‘YourColumn’, ‘TableA’,) but it is not recommended because it will cause SQL injection, so It’s best to write it all.

Small tips:
SQL injection (SQL Injection) is a common computer security vulnerability that allows attackers to insert malicious SQL queries into an application’s database. This vulnerability typically occurs in applications that do not properly validate user input or dynamically generate SQL queries. An attacker can exploit SQL injection vulnerabilities to perform unauthorized database operations or access sensitive information by maliciously constructing input data. SQL injection can cause the following problems:
1. Data leakage: Attackers can query sensitive data such as user credentials, credit card information, personally identifiable information, etc. through SQL injection.
2. Data tampering: Attackers can modify the data in the database, such as deleting, modifying or adding records.
3. Denial of Service: An attacker can execute malicious SQL queries, causing database performance degradation or crash, thereby affecting application availability.

SQL injection usually occurs when an application does not validate and handle user input properly. An attacker would insert malicious SQL code into an application's input fields in an attempt to bypass the application's authentication and access the database. To prevent SQL injection, developers should take the following steps:
  1. Use parameterized queries: Using parameterized queries prevents user input from being interpreted as SQL code. Parameterized queries treat user-supplied input as data values rather than as part of the SQL code.
  2. Input validation: Before accepting user input, perform validation and filtering to ensure that the input data conforms to the expected format and range. Untrusted input should be rejected.
  3. Principle of least privilege: Database users and applications should be assigned the minimum necessary privileges to limit inappropriate access to the database.
  4. Error handling: Avoid exposing detailed error messages to the user, as this may provide an attacker with information about the database structure.
  5. Secure Development Practices: Follow secure development practices, including regular code reviews for potential SQL injection vulnerabilities.
  6. By taking these precautions, you can reduce the risk of SQL injection and improve application and database security.

2. Stored procedure with all parameters

CREATE PROC CSP_Top20
    @URIDINT
  , @BYEAR SMALLDATETIME
  , @CURRENCY INT
  , @SortColumn NVARCHAR(500) -- Sorting field
  , @SortDirection NVARCHAR(4) -- sorting direction ASC-ascending | DESC-descending
AS
begin
DECLARE @DynamicSQL NVARCHAR(MAX)

     DELETE FROM CTop20 WHERE URID = @URID

SET @DynamicSQL = '
    SELECT TOP 20 @URID, ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ' ' + @SortDirection + ') AS RowNum,
           coloumn1,coloumn2,coloumn3
        FROM CVW_Top20
        WHERE 1=1'


    if @BYEAR IS NOT NULL
        SET @DynamicSQL = @DynamicSQL + ' AND DATEDIFF(YEAR, BYear, @BYEAR) = 0'

    if @CURRENCY IS NOT NULL
        SET @DynamicSQL = @DynamicSQL + ' AND Currency_ID = @CURRENCY'


    INSERT INTO CTop20 (URID, RowNum, coloumn1,coloumn2,coloumn3 )
    EXEC sp_executesql @DynamicSQL, N'@CURRENCY INT, @BYEAR SMALLDATETIME, @URID INT', @CURRENCY, @BYEAR, @URID
    --All parameters are written here
    
    SELECT * FROM CTop20 WHERE URID = @URID

end