sql error. i have used sql server 2012

Question

ALTER PROCEDURE combined_report_generation
AS
BEGIN

create table temp(id int identity(1,1),form_id varchar(50),process_id varchar(50),active_status int);

insert into temp(form_id,process_id,active_status) select form_id,process_id,active_status from audit_form_active;


DECLARE @MAXID INT, @Counter INT,@FI varchar(50),@PI varchar(50),@match varchar(50),@tablename varchar(100),@match_col varchar(50)
SET @COUNTER = 2
SELECT @MAXID = COUNT(*) FROM temp where active_status=1



WHILE (@COUNTER <= @MAXID)
BEGIN

SELECT @FI=form_id FROM temp where id=@COUNTER
SELECT @PI=process_id FROM temp where id=@COUNTER
SELECT @match=cdr_match_col FROM mapping_table where process_id=(select process_id FROM temp where id=@COUNTER)
SET @match_col='cdr_'+@match
SET @tablename='AUDITFORM_'+@FI


INSERT INTO combined_report
(

      [audit_id]
      ,[form_id]
      ,[audit_score]
      ,[scorable_value]
      ,[scoring_value]
      ,[UniqueColumnMappingCdr]
      ,[crm_AgentId]
      ,[crm_AgentName]
      ,[voice_record_id]
      ,[fatal_count]
      ,[fatal_status]
      ,[agent_disposition]
      ,[agent_disposition_status]
      ,[updated_status]
      ,[TeamLeader]
      ,[calibration_call]
      ,[sample_to]
      ,[created_by]
      ,[agent_disposition_date]
      ,[Audit_Start_Time]
      ,[created_on]
      ,[updated_by]
      ,[updated_on]
)

EXEC('
SELECT 
      [audit_id]
      ,[form_id]
      ,[audit_score]
      ,[scorable_value]
      ,[scoring_value]
      ,'+@match_col+'
      ,[crm_AgentId]
      ,[crm_AgentName]
      ,[voice_record_id]
      ,[fatal_count]
      ,[fatal_status]
      ,[agent_disposition]
      ,[agent_disposition_status]
      ,[updated_status]
      ,[TeamLeader]
      ,[calibration_call]
      ,[sample_to]
      ,[created_by]
      ,[agent_disposition_date]
      ,[Audit_Start_Time]
      ,[created_on]
      ,[updated_by]
      ,[updated_on]
  FROM '+@tablename+'where  cast(updated_on as datetime)  >(select MAX(cast(updated_on as datetime)) from combined_report)')

  SET @COUNTER=@COUNTER+1
END
DROP table temp
END
------

m getting this error ,i used sql server 2012 "updated_on" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

alter database database_name set compatibility_level=90

Answer 1

When executing dynamic SQL, make sure there are spaces in between your objects and your statement keywords. Your table name is being merged with the WHERE keyword, put a space in between.

Also, when using dynamic object names like columns or tables, make sure they are correctly escaped by using QUOTENAME() function. Your dynamic values might have a space or a weird character you need to enclose in brackets.

SET @match_col = QUOTENAME(@match_col)
SET @tablename = QUOTENAME(@tablename)

To further debug the error, you can try asigning your dynamic SQL to a variable and switch between PRINT or EXEC:

DECLARE @DynamicSQL VARCHAR(MAX) = 'SELECT ....'

-- Debug
PRINT (@DynamicSQL)

-- Run
EXEC (@DynamicSQL)

Answer 2

You just need a space before where

EXEC('
SELECT 
      [audit_id]
      ,[form_id]
      ,[audit_score]
      ,[scorable_value]
      ,[scoring_value]
      ,['+@match_col+']
      ,[crm_AgentId]
      ,[crm_AgentName]
      ,[voice_record_id]
      ,[fatal_count]
      ,[fatal_status]
      ,[agent_disposition]
      ,[agent_disposition_status]
      ,[updated_status]
      ,[TeamLeader]
      ,[calibration_call]
      ,[sample_to]
      ,[created_by]
      ,[agent_disposition_date]
      ,[Audit_Start_Time]
      ,[created_on]
      ,[updated_by]
      ,[updated_on]
  FROM [' + @tablename + '] where  cast(updated_on as datetime)  >(select MAX(cast(updated_on as datetime)) from combined_report)')