DECLARE @DROP_SQL NVARCHAR(MAX)
DECLARE @create_Sql NVARCHAR(MAX)
DECLARE @tablename2 NVARCHAR(MAX)
DECLARE @insert_sql NVARCHAR(MAX)
DECLARE @db_schema NVARCHAR(MAX)
SET @db_schema='GGW'
SET @tablename2='VIEW_LOG'
DECLARE @count INT
SET @count=1

DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
SET @StartTime =GETDATE()
SET @EndTime=GETDATE()

DECLARE @schema_nm NVARCHAR(MAX)
SET @schema_nm='GW'

DECLARE @tablename NVARCHAR(MAX)
SET @tablename='x'


SET @drop_sql = 'DROP TABLE IF EXISTS [GW].[' @tablename2 ']'
PRINT @drop_sql
EXEC(@drop_sql)


SET @create_Sql = 'CREATE TABLE [GGW].[VIEW_LOG] (
        schema_name NVARCHAR(MAX),
        view_name NVARCHAR(max)
        ,row_count INT
        ,error_msg NVARCHAR(max)
        ,query_start_time DATETIME
        ,query_end_time DATETIME
        ,execution_time_in_ms NVARCHAR(max)
        );'
PRINT @create_Sql
EXEC(@create_Sql)



SET @insert_sql='INSERT INTO [' @db_schema '].[' @tablename2 '] SELECT '  @schema_nm ',' @tablename ',' CONVERT(NVARCHAR,@count) ',' NULL ',' CONVERT(VARCHAR, @StartTime) ',' CONVERT(VARCHAR, @EndTime) ',' CONVERT(VARCHAR, DATEDIFF(ms,@StartTime,@EndTime)) 
PRINT(@insert_sql)
EXEC(@insert_sql)

I have created a table and trying to insert some records in to the table. the insert statement is assigned to a variable which is @insert_sql. i am trying to execute the sql that i have created and assigned to variable but it couldn't be printed or executed. am i missing something here?

CodePudding user response：

There's so much wrong with the above... The comments under the question touch why the above fails, you concatenate NULL to your string which results in NULL, and I highlight some more below and in the comments of the SQL. Of course, you shouldn't be using that concatenation at all and should be parametrising your parameters.

There's a lot of inconsistencies in the SQL too; varying references to schema names, some hard coded others not. I've replaced any schema references with dbo to avoid incorrect assumptions.

I also inject your object names, where there is dynamic SQL, safely with QUOTENAME. '[' @SomeVariable ']' is NOT injection safe; especially when you give someone 2GB/~1 billion characters to "play" with. A right bracket (]) can just as easily be escaped as a single quote ('), and with 2GB of data, someone malicious can do literally what ever they want; your code is a security vulnerability waited to be exploited.

I make some assumptions on data types, and I've not tested this, but it should, at least, get you much closer to the desired behaviour.

DECLARE @DROP_SQL nvarchar(MAX),
        @create_Sql nvarchar(MAX),
        @tablename2 sysname,
        @insert_sql nvarchar(MAX),
        @db_schema sysname;
SET @db_schema = N'dbo';
SET @tablename2 = N'VIEW_LOG';
DECLARE @count int;
SET @count = 1;

DECLARE @StartTime datetime,
        @EndTime datetime;
SET @StartTime = GETDATE();
SET @EndTime = GETDATE();

DECLARE @schema_nm sysname;
SET @schema_nm = 'dbo';

DECLARE @tablename sysname;
SET @tablename = 'x';


SET @DROP_SQL = 'DROP TABLE IF EXISTS '   QUOTENAME(@db_schema)   N'.'   QUOTENAME(@tablename2)   N';';
--PRINT @drop_sql
EXEC sys.sp_executesql @SQL;

--This query wasn't dynamic, so there's no point making it dynamic.
--Though the data types are all wrong here, see the comments below, but I create it with more correct data types
/*
CREATE TABLE [dbo].[VIEW_LOG] (
        schema_name NVARCHAR(MAX), --Should this really be sysname?
        view_name NVARCHAR(max), --Should this really be sysname?
        row_count INT,
        error_msg NVARCHAR(max), --Is your error really likely to be 4,000 characters or more? 
        query_start_time DATETIME,
        query_end_time DATETIME,
        execution_time_in_ms NVARCHAR(max)); --If it's time in ms, why is this an nvarchar(MAX)? Stop (ab)using nvarchar(MAX)
*/
CREATE TABLE [dbo].[VIEW_LOG] (schema_name sysname,
                               view_name sysname,
                               row_count int,
                               error_msg nvarchar(4000),
                               query_start_time datetime,
                               query_end_time datetime,
                               execution_time_in_ms int);
--PRINT @db_schema
--PRINT @tablename2
DECLARE @CRLF nchar(2) = NCHAR(13)   NCHAR(10); --Let's add some whitespace and line breaks to this query

SET @insert_sql = N'INSERT INTO '   QUOTENAME(@db_schema)   N'.'   QUOTENAME(@tablename2)   N' (schema_name, view_name, row_count, error_msg, query_start_time, query_end_time, execution_time_in_ms)'   @CRLF   N'VALUES(@schema_nm,@tablename,@count,NULL,@StartTime,@EndTime,DATEDIFF(ms,@StartTime,@EndTime));';
--PRINT(@insert_sql)
EXEC sys.sp_executesql @SQL,
                       N'@schema_nm sysname,@tablename sysname,@count int,@StartTime datetime,@EndTime datetime',
                       @schema_nm,
                       @tablename,
                       @count,
                       @StartTime,
                       @EndTime;