@SQL Formatting problem (1 Viewer)

Tupacmoche

Registered User.
Local time
Today, 04:28
Joined
Apr 28, 2008
Messages
291
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

Wino Moderator
Staff member
Local time
Today, 01:28
Joined
Aug 30, 2003
Messages
36,118
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

AWF VIP
Local time
Today, 08:28
Joined
Jul 26, 2013
Messages
10,353
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

Super Moderator
Staff member
Local time
Today, 08:28
Joined
Feb 19, 2013
Messages
16,553
looks like there is also a missing ) in the update sql where clause as well
 

Minty

AWF VIP
Local time
Today, 08:28
Joined
Jul 26, 2013
Messages
10,353
I also think the delete statement isn't correct - try looking at the output from this;
PHP:
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
 

Users who are viewing this thread

Top Bottom