I need one task to resolve the issue. The problem we need to solve is write a SQL query to pass input as EnityName and get binary / josn data from that query. I tried the below code but getting error:
-- Construct the dynamic SQL query
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM ' + QUOTENAME({TableName});
-- Execute the dynamic SQL query
EXEC sp_executesql @SQL;
Hi @Rammurthy Naidu Boddu,
The issue arises because the SQL query doesn't handle input sanitization properly, which can lead to errors or even SQL injection risks.
Updated SQL Code:
DECLARE @TableName NVARCHAR(255);
-- Set the input table name (replace this with your actual parameter)
SET @TableName = 'YourTableName';
-- Construct the dynamic SQL query safely
SET @SQL = '
SELECT *
FROM ' + QUOTENAME(@TableName) + '
FOR JSON PATH;';
Thanks,
Vipin Yadav
Hi @Vipin Yadav, Till i am getting error Database returned the following error: Error in advanced query SQL1: The variable name '@qpstSQL' has already been declared. Variable names must be unique within a query batch or stored procedure. The variable name '@qpstTableName' has already been declared. Variable names must be unique within a query batch or stored procedure.
The error you're encountering indicates that the variable names @qpstSQL and @qpstTableName are being declared multiple times within the same SQL batch or procedure. This often happens when the query is executed inside a loop or multiple invocations without proper scope isolation.
Corrected SQL Code (Avoid Duplicate Declarations) -
-- Check if variables already exist and drop them
IF OBJECT_ID('tempdb..#tempSQLVars') IS NOT NULL
DROP TABLE #tempSQLVars;
-- Create temp table to hold dynamic SQL and table name
CREATE TABLE #tempSQLVars (SQL NVARCHAR(MAX), TableName NVARCHAR(255));
-- Insert values into the temp table (or directly set variables)
INSERT INTO #tempSQLVars VALUES ('', 'YourTableName');
-- Declare variables
-- Set variables from the temp table
SELECT @TableName = TableName FROM #tempSQLVars;
SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' FOR JSON PATH;';
-- Clean up temp table
Hi @Vipin Yadav, I checked in ChatGPT and Copilot answers and tested all the thinks. Not working. The code which you placed is from any one of the them.
Hi @Rammurthy Naidu Boddu ,
I have encountered a similar issue before, which is why I offered the solution, thinking you might be experiencing the same problem.
Can you please share some screen shot so that i will check.