@SQL Formatting problem

[Tupacmoche]

Hi SQL Masters,

I'm having a problem with terminating statements in the @sql script section. The problem is only in the section run by the EXEC (@SQL). Can anyone see what I'm doing wrong.

USE Temp_Upload_Tables
DECLARE @TableCount tinyint
DECLARE @SQL varchar(2000)
DECLARE @TableName varchar(100)
DECLARE @TableID varchar(6)
DECLARE @TblAlias Varchar(1)
--------------------------------------------------
/* First create temp table and insert values */
/* This tbl will be used to loop through the */
/* tables that are updated. */
CREATE TABLE #TableList
(
TableName Varchar(100),
TableID Varchar(6),
TableA Varchar(1)
)
INSERT INTO #TableList(TableName, TableID, TableA)
VALUES ('tmpEpic_Name','EN','m');
INSERT INTO #TableList(TableName, TableID, TableA)
VALUES ('tmpEpic_history','EH','h');
INSERT INTO #TableList(TableName, TableID, TableA)
VALUES ('tmpEpic_screening_history','ESH','s');
INSERT INTO #TableList(TableName, TableID, TableA)
VALUES ('tmpEpic_request_history','ERH','r');
-----------------------------------------------------
/* This section loops through the Epic tables and updates the MRN */
SELECT @TableCount = COUNT(1) from #TableList
WHILE @TableCount > 0
BEGIN

Select Top 1 @TableName = TableName, @TableID = TableID From #TableList

SET @SQL = 'Delete ' + @TableName + '
Where MRN IN
(Select SourcePatientMRN
from MRN_Processing
Where Processing_code = 2 and Tbl_ID = ''' + @TableID + '''

Update ' + @TableName + ' Set MRN2 = TargetPatientMRN
from ' + @TableName + ' as e
inner join MRN_Processing m
on e.MRN = m.SourcePatientMRN
Where (SourceIs = 1 and Processing_code = 1 and Tbl_ID = ''' + @TableID + ''';'

EXEC (@SQL)

DELETE #TableList WHERE TableName = @TableName
SELECT @TableCount = COUNT(1) from #TableList

END

Here is the error message:

(1 row(s) affected)
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Update'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ';'.
(1 row(s) affected)
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Update'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ';'.
(1 row(s) affected)
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Update'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ';'.
(1 row(s) affected)
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Update'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ';'.

 

[pbaldy]

It appears to be executing the delete but not the update? Have you tried without the semi colon? I never include them. I've never used dynamic SQL in SQL Server, but this should show you the resulting SQL:

PRINT @SQL

 

[Minty]

To echo what Paul said - I saw the error using a Print statement on your earlier post.

You are missing a closing ) at the end of your DELETE SQL IN Subquery.

I don't think you can execute 2 statements like that in one EXEC. Try separating them into to two distinct EXEC's

 

[CJ_London]

looks like there is also a missing ) in the update sql where clause as well

 

[Minty]

I also think the delete statement isn't correct - try looking at the output from this;

DECLARE @TableCount tinyint 
DECLARE @SQL varchar(2000)
DECLARE @SQL2 varchar(2000)
DECLARE @TableName varchar(100)
DECLARE @TableID varchar(6)
DECLARE @TblAlias Varchar(1)
--------------------------------------------------
/* First create temp table and insert values */
/* This tbl will be used to loop through the */
/* tables that are updated. */
--IF EXISTS (SELECT * FROM #TableList) 
DROP TABLE #Tablelist

CREATE TABLE #TableList
(
TableName Varchar(100),
TableID Varchar(6),
TableA Varchar(1)
)
INSERT INTO #TableList(TableName, TableID, TableA)
VALUES ('tmpEpic_Name','EN','m');
INSERT INTO #TableList(TableName, TableID, TableA)
VALUES ('tmpEpic_history','EH','h');
INSERT INTO #TableList(TableName, TableID, TableA)
VALUES ('tmpEpic_screening_history','ESH','s');
INSERT INTO #TableList(TableName, TableID, TableA)
VALUES ('tmpEpic_request_history','ERH','r');
-----------------------------------------------------
/* This section loops through the Epic tables and updates the MRN */
SELECT @TableCount = COUNT(1) from #TableList
WHILE @TableCount > 0 
BEGIN

Select Top 1 @TableName = TableName, @TableID = TableID From #TableList

SET @SQL = 'Delete ' + @TableName + ' 
Where MRN IN 
(Select SourcePatientMRN
from MRN_Processing 
Where Processing_code = 2 and Tbl_ID = ''' + @TableID + ''') '


SET @SQL2 = 'Update ' + @TableName + ' Set MRN2 = TargetPatientMRN 
from ' + @TableName + ' as e
inner join MRN_Processing m 
on e.MRN = m.SourcePatientMRN 
Where (SourceIs = 1 and Processing_code = 1 and Tbl_ID = ''' + @TableID + ''') ' 


PRINT @SQL2
EXEC (@SQL2)
PRINT @SQL

EXEC (@SQL)
DELETE #TableList WHERE TableName = @TableName
SELECT @TableCount = COUNT(1) from #TableList 

END