1.EXEC execution of SQL statement truncated problem:
SQL statements:
1 SET @sqlSel='SELECT'+@sqlField+',2 SUM (ISNULL (b.customstariff_sup,0)) as Customstariff_sup,sum (ISNULL (addedvaluetax_sup,0)) as Addedvaluetax_sup , SUM (ISNULL (consumpttax_sup,0)) as Consumpttax_sup,3 SUM (ISNULL (customstariff_ref,0)) as Customstariff_ref,sum (ISNULL (addedvaluetax_ref,0)) as Addedvaluetax_ref, SUM (ISNULL (consumpttax_ref,0)) as Consumpttax_ref,4 SUM (ISNULL (customs_sup,0)) as Customs_sup,sum (ISNULL (addedvalue_sup,0)) as Addedvalue_sup,sum (ISNULL (consumpt_ sup,0) as Consumpt_sup,5 SUM (ISNULL (customs_ref,0)) as Customs_ref,sum (ISNULL (addedvalue_ref,0)) as Addedvalue_ref,sum (ISNULL (consumpt_ ref,0) as Consumpt_ref,6 SUM (ISNULL (customs_nosupref,0)) as Customs_nosupref,sum (ISNULL (addedvalue_nosupref,0)) as Addedvalue_nosupref, SUM (ISNULL (consumpt_nosupref,0)) as Consumpt_nosupref7 From dbo. Classifycorrectionreporthead h8 Right join dbo. Classifycorrectionreportbody B on H.id=b.headid9 where'+@sqlWhere+'GROUP BY Reportrowhead'Ten One Print @sqlSel A exec @sqlSel
--Note: The@sqlSel is a variable of type varchar (8000),@sqlField the variable type is varchar ($),@sqlWhere the variable type is varchar (+)
An error was found during the execution of the stored procedure "... Unrecognized character ": The check found that the SQL statement was truncated,
First check if the length of the SQL statement exceeds the length of the variable definition (obviously not exceeded)
The SQL statement can then be printed in its entirety and executed without errors, at which point it is possible to judge the problem of exec.
Solution:
Add () on the SQL variable that is executed;
exec (@sqlSel)
2. Clear table Data efficiently:TRUNCATE tables
Compare to delete:
(1). DELETE
? DML language
? can be rolled back
? can be conditionally deleted
Usage: DELETE from table name
WHERE condition
(2). TRUNCATE TABLE
? DD2. TRUNCATE TABLE
? DDL language
? cannot be rolled back
? All table contents are deleted by default
Deletes faster than delete.
Usage: TRUNCATE table name
SQL Server stored procedure execution encountered problems and solutions