Logic Visualizer | SQL

Dynamic SQL Query Execution

This code performs dynamic SQL query execution with input value as a parameter. It includes error handling and logging.


Empty image or helper icon

This Query related with thread "Guidelines for Working with Dynamic SQL Queries"

Prompt

-- Use parameterized query and input validation
DECLARE @inputValue NVARCHAR(50);
SET @inputValue = 'Robert''; DROP TABLE Student;--';

DECLARE @sqlQuery NVARCHAR(MAX);
SET @sqlQuery = N'SELECT * FROM Students WHERE Name = @name';

-- Execute dynamic SQL with parameterized query
EXEC sp_executesql @sqlQuery, N'@name NVARCHAR(50)', @name = @inputValue;

-- Error handling
BEGIN TRY
    EXEC sp_executesql @sqlQuery, N'@name NVARCHAR(50)', @name = @inputValue;
END TRY
BEGIN CATCH
    -- Log and handle the error
    SELECT ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE();
END CATCH

-- Perform testing with different input values and edge cases
DECLARE @parameterValue NVARCHAR(50);
SET @parameterValue = 'John';

SET @sqlQuery = N'SELECT * FROM Students WHERE Name = @name';

EXEC sp_executesql @sqlQuery, N'@name NVARCHAR(50)', @name = @parameterValue;

Answer

START
┌─────┐
│Code │
└───┬─┘
    │
    │
    ▼
┌────────────────────┐
│ Set inputValue     │
│ to 'Robert''; DROP  │
│ TABLE Student;--'  │
└───┬────────────────┘
    │
    │
    ▼
┌─────┐
│Code │
└───┬─┘
    │
    │
    ▼
┌───────────────┐
│ Set sqlQuery   │
│ to 'SELECT... │
│  WHERE Name...│
└───┬───────────┘
    │
    │
    ▼
┌────────────────────┐
│ Execute the dynamic│
│ SQL query with     │
│ parameterized query│
│ and input value    │
└───┬────────────────┘
    │
    │
    ▼
┌─────┐
│Code │
└───┬─┘
    │
    │
    ▼
┌───────────────────────┐
│ Error handling        │
│ - Attempt executing  │
│   the dynamic SQL    │
│   with input value   │
└───┬───────────────────┘
    │
    │
    ▼
┌─────┐
│Code │
└───┬─┘
    │
    │
    ▼
┌────────┐
│ Log and│
│ handle │
│ the    │
│ error  │
└───┬────┘
    │
    │
    ▼
┌──────────┐
│ SELECT...│
│ ERROR... │
└───┬──────┘
    │
    │
    ▼
┌─────┐
│Code │
└───┬─┘
    │
    │
    ▼
┌────────────────────┐
│ Set parameterValue  │
│ to 'John'          │
└───┬────────────────┘
    │
    │
    ▼
┌───────────────┐
│ Set sqlQuery   │
│ to 'SELECT... │
│  WHERE Name...│
└───┬───────────┘
    │
    │
    ▼
┌───────────────────────┐
│ Execute the dynamic   │
│ SQL query with       │
│ parameterized query  │
│ and input value      │
└───┬───────────────────┘
    │
    │
    ▼
┌─────┐
│Code │
└───┬─┘
    │
    │
    ▼
┌────────┐
│ Result │
└───────┘
    │
    │
    ▼
END

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

The given code demonstrates the process of executing dynamic SQL queries using parameterized queries. It starts with setting the inputValue to a specific value, followed by setting the sqlQuery to a SELECT statement with a WHERE condition. The dynamic SQL query is then executed with the input value as a parameter. The code also includes error handling, where any errors in executing the dynamic SQL query are logged and handled accordingly. Finally, the result of the SQL query execution is obtained. This code showcases a common approach to executing dynamic SQL queries and handling potential errors during the process.

More Logic Visualizers
Apache Flink Logic VisualizerApache Pig Logic VisualizerAzure Data Factory Logic VisualizerC/C++ Logic VisualizerCouchDB Logic VisualizerDAX Logic VisualizerExcel Logic VisualizerFirebase Logic VisualizerGoogle BigQuery Logic VisualizerGoogle Sheets Logic VisualizerGraphQL Logic VisualizerHive Logic VisualizerJava Logic VisualizerJavaScript Logic VisualizerJulia Logic VisualizerLua Logic VisualizerM (Power Query) Logic VisualizerMATLAB Logic VisualizerMongoDB Logic VisualizerOracle Logic VisualizerPostgreSQL Logic VisualizerPower BI Logic VisualizerPython Logic VisualizerR Logic VisualizerRedis Logic VisualizerRegex Logic VisualizerRuby Logic VisualizerSAS Logic VisualizerScala Logic VisualizerShell Logic VisualizerSPSS Logic VisualizerSQL Logic VisualizerSQLite Logic VisualizerStata Logic VisualizerTableau Logic VisualizerVBA Logic Visualizer