How to block of code recursivly (1 Viewer)

Tupacmoche

Registered User.
Local time
Today, 04:43
Joined
Apr 28, 2008
Messages
291
Hi SQL Masters,

I have a block of code that work perfectly but need to run it against dozens of tables. It does not make any sense to repeat it. What structure can, I use to run this code and loop through many different tables? See code below. Besides the table name changing, the tbl_id in the case statement will also change.

Select SourcePatientMRN
,SourceIs =(Case When e.Match = 'Y' then 1 else 0 end)
,
TargetIs =(Case When n.Match = 'Y' then 1 else 0 end)
,
tbl_id =(Case when e.Match = 'Y' then 'EN' else 'EN' end)
from MRN_Processing as m
Left join
(Select *, 'Y' as Match
from tmpEpic_Name
) as e on e.MRN = m.SourcePatientMRN
Left join
( Select *, 'Y' as Match
from tmpEpic_Name
) as n on n.MRN = m.TargetPatientMRN
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:43
Joined
Jul 9, 2003
Messages
16,245
Hi SQL Masters,

I have a block of code that work perfectly but need to run it against dozens of tables.

I think I would build the SQL statement with VBA. Replace the table name here "MRN_Processing! With a table name stored in a in another table called tblTableNames. It might well be possible to hold the other changeable information in the table tblTableNames.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:43
Joined
Jan 20, 2009
Messages
12,849
Use a stored procedure and pass the table name as a parameter.

The procedure will use sp_executesql to run the dynamically created sql command.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:43
Joined
Jul 9, 2003
Messages
16,245
Oop's, I didn't see that! This is an SQL Server thread...
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:43
Joined
Jan 20, 2009
Messages
12,849
What exactly are you doing with the results of the query? Just looking at them, copying to some other application, linking from Excel or Access etc?
 

Tupacmoche

Registered User.
Local time
Today, 04:43
Joined
Apr 28, 2008
Messages
291
Basically, the data in the MRN_Processing table is being joined to many other tables to update them. I used a select for testing but the script, I sent will be an update query. The table name in the script is tmpEpic_Name which is one of the tables but there are many others. So, the sp would process the first table updating based on the join then move on to the next table and then the next until all the tables are updated. The tables are in MSSQL.
 

Tupacmoche

Registered User.
Local time
Today, 04:43
Joined
Apr 28, 2008
Messages
291
Hi All,

I came up with the script to run my query for all the tables necessary tested it and worked fine. But, now that I have put all the pieces together, I'm getting an Incorrect syntax near 'Y'. Can anyone see what is wrong?

Error msg:
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'Y'.


USE Temp_Upload_Tables
--DROP TABLE #TableList
DECLARE @TableCount tinyint
DECLARE @SQL varchar(500)
DECLARE @TableName varchar(40)
DECLARE @Tbl_id 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(40),
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');
-----------------------------------------------------
SELECT @TableCount = COUNT(1) from #TableList
WHILE @TableCount > 0
BEGIN
SELECT Top 1 @TableName = TableName from #TableList ORDER BY TableName

SELECT @SQL = 'Select SourcePatientMRN,
SourceIs = (Case When e.Match = '
Y' then 1 else 0 end),
TargetIs = (Case When n.Match = '
Y' then 1 else 0 end),
tbl_id = (Case when e.Match = '
Y' then' + @Tbl_id 'else' + @Tbl_id 'end)

from MRN_Processing as m'

'Left join
(Select *, '
Y' as Match
from'
+ @TableName '
) as e on e.MRN = m.SourcePatientMRN
Left join
( Select *, '
Y' as Match
from'
+ @TableName
') as n on n.MRN = m.TargetPatientMRN'

EXEC (@SQL)

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

END


 

Mark_

Longboard on the internet
Local time
Today, 01:43
Joined
Sep 12, 2017
Messages
2,111
Can't answer for other pieces, but shouldn't
Code:
tbl_id = (Case when e.Match = 'Y' then' + @Tbl_id 'else' + @Tbl_id 'end)
simply be
Code:
tbl_id =  @Tbl_id
Not sure how you are using the Case in this case.....
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:43
Joined
Jan 20, 2009
Messages
12,849
You need to include a space after each 'from' in the SQL string.

Code:
EXEC sys.sp_executesql @SQL;
is preferred to
Code:
EXEC (@SQL)

Parameters can be used with sp_executesql, improving performance because the query plan can be reused.

It us good practice to terminate each command with a semicolon.

It is more usual (and ANSI standard) to use SET rather than SELECT when assigning a value to a single variable.

You can declare multiple variables separated with commas in one DECLARE.
 

Users who are viewing this thread

Top Bottom