使用SQL Server并在SET@SQL命令处获取语法错误

Question

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[EasyMetrics]
AS
DECLARE @sql VARCHAR(2000)
--drop existing temp tables
IF object_id('tempdb..#mkg_metrics') IS NOT NULL
DROP TABLE #mkg_metrics
--create temp working tables
CREATE TABLE #mkg_metrics 
(
TR_EM_NO CHAR(11),
TR_DATE_TIME DATETIME,
TRAN_TYPE CHAR(4),
QUANTITY NUMERIC(10,0),
TR_STAGE_LOCATION CHAR(15),
TR_loca CHAR(10),
DOCK CHAR(2),
VD_NAME VARCHAR(36)

--Pull fields out of large tables into temp table for faster query
SET @sql = "INSERT INTO #mkg_metrics
SELECT TR_EM_NO, TR_DATE_TIME, TRAN_TYPE, QUANTITY, TR_STAGE_LOCATION, TR_loca
FROM LOGTRAN 
INNER JOIN TRAILER_LOG tl
WHERE TR_DATE_TIME >= DATEADD(DAY, -1, getdate()) 
AND TR_DATE_TIME <= GETDATE() 
AND tl.TRAN_TYPE = 'SETUP'
INSERT INTO #mkg_metrics
SELECT
tl.DOCK
FROM
LOGTRAN 
INNER JOIN
TRAILER_LOG tl
WHERE
TR_DATE_TIME >= DATEADD(DAY, -1, GETDATE()) 
AND TR_DATE_TIME <= GETDATE() 
AND tl.TRAN_TYPE = 'SETUP'
INSERT INTO #mkg_metrics
SELECT 
vm.VD_NAME
FROM
LOGTRAN 
LEFT OUTER JOIN
IMMAS im ON im.VD_NO = vm.VD_NO 
LEFT OUTER JOIN
TRAILER_LOG tl
WHERE
TR_DATE_TIME >= DATEADD(DAY, -1, GETDATE()) 
AND TR_DATE_TIME <= GETDATE() 
AND tl.TRAN_TYPE = 'SETUP'
--print @sql
exec(@sql)
SELECT DISTICNT
TR_EM_NO, TR_DATE_TIME, TRAN_TYPE, QUANTITY, TR_STAGE_LOCATION, 
TR_loca, DOCK, VD_NAME
FROM
mkg_metrics 
ORDER BY
TR_DATE_TIME

Answer 1

create table #mkg_metrics语句末尾没有右括号。

create table #mkg_metrics (TR_EM_NO char(11),TR_DATE_TIME datetime,TRAN_TYPE char(4),QUANTITY numeric(10,0),TR_STAGE_LOCATION char(15),TR_loca char(10),DOCK char(2),
VD_NAME varchar(36))

Answer 2

SQL演示

对字符串文字使用单引号，并两次转义单引号

declare @sql varchar(max);
set @sql = '
insert into #mkg_metrics
select TR_EM_NO, TR_DATE_TIME, TRAN_TYPE, QUANTITY, TR_STAGE_LOCATION, TR_loca
from LOGTRAN inner join TRAILER_LOG tl
where TR_DATE_TIME >= DATEADD(DAY, -1, getdate()) and TR_DATE_TIME <= GETDATE() and tl.TRAN_TYPE = ''SETUP''
insert into #mkg_metrics
select tl.DOCK
from LOGTRAN inner join TRAILER_LOG tl
where TR_DATE_TIME >= DATEADD(DAY, -1, getdate()) and TR_DATE_TIME <= GETDATE() and tl.TRAN_TYPE = ''SETUP''
insert into #mkg_metrics
select vm.VD_NAME
from LOGTRAN left outer join IMMAS im
on im.VD_NO = vm.VD_NO left outer join TRAILER_LOG tl
where TR_DATE_TIME >= DATEADD(DAY, -1, getdate()) and TR_DATE_TIME <= GETDATE() and tl.TRAN_TYPE = ''SETUP''
';
SELECT @sql;