Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-08-2017, 11:58 AM   #1
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 157
Thanks: 40
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
@SQL Formatting problem

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 ';'.

Tupacmoche is offline   Reply With Quote
Old 11-08-2017, 12:32 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,929
Thanks: 9
Thanked 3,844 Times in 3,787 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: @SQL Formatting problem

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
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Tupacmoche (11-09-2017)
Old 11-09-2017, 01:33 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,598
Thanks: 134
Thanked 1,514 Times in 1,486 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: @SQL Formatting problem

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

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tupacmoche (11-09-2017)
Old 11-09-2017, 01:50 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,326
Thanks: 39
Thanked 3,343 Times in 3,238 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: @SQL Formatting problem

looks like there is also a missing ) in the update sql where clause as well
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Tupacmoche (11-09-2017)
Old 11-09-2017, 02:03 AM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,598
Thanks: 134
Thanked 1,514 Times in 1,486 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: @SQL Formatting problem

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

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(1from #TableList
WHILE @TableCount 
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(1from #TableList 

END 

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tupacmoche (11-09-2017)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Default formatting option in Conditional formatting Dialog? prabha_friend Forms 11 08-04-2014 05:18 AM
Please Help! I have a formatting problem smichaels1234 Reports 2 06-19-2008 10:26 AM
Formatting Problem hewstone999 Modules & VBA 4 03-11-2008 05:30 AM
Formatting problem lipin Reports 2 10-22-2002 12:12 PM
Formatting problem mugman17 Reports 1 04-03-2002 01:10 PM




All times are GMT -8. The time now is 08:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World