Variable in where clause not working

[Tupacmoche]

Hi SQL Masters,

I have a straight forward SQL code that, I decided to add a variable to the where clause. I have tried writing it many different ways but keep getting an error message. Before, making it into a variable it worked fine but now, I get the error message:

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@TableID".
(1 row(s) affected)
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@TableID".
(1 row(s) affected)
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@TableID".
(1 row(s) affected)
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@TableID".
(1 row(s) affected)

But the variable is declared. Here is the code:

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');
-----------------------------------------------------
SELECT @TableCount = COUNT(1) from #TableList
WHILE @TableCount > 0
BEGIN

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

SET @SQL = 'Update MRN_Processing
SET SourceIs = (Case When e.Match = ''Y'' then 1 else 0 end),
TargetIs = (Case When n.Match = ''Y'' then 1 else 0 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
Where tbl_id = + @TableID'

EXEC (@SQL)

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

END

Does anyone see what is wrong?

 

[Minty]

This line doesn't make a lot of sense ?

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

And how did you get all those pretty colours to remain intact?

 

[Tupacmoche]

I copied straight out of Management studio so it may have retained the color format. As to the code:

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

I'm assigning the columns from the first row temp table into two variables. You will notice that, I load the table in the code. So, @TableName is assigned 'tmpEpic_Name, @TableID is assigned 'EN'. So, as you know saying Top 1 is like filtering for the first row.

 

[Minty]

Weird , I've never used that syntax to set a variable in SQL.

Any ways - I think your issue is a missing '

from ' + @TableName + '
) as n on n.MRN = m.TargetPatientMRN
Where tbl_id = + @TableID'     <<<<<<

The pretties helped me spot it

 

[Tupacmoche]

Where is it missing ?

 

[Minty]

It should read like this - the string concatenation of the tableID was out

SET @SQL = 'Update MRN_Processing 
SET SourceIs = (Case When e.Match = ''Y'' then 1 else 0 end), 
TargetIs = (Case When n.Match = ''Y'' then 1 else 0 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
[COLOR="Red"]Where tbl_id = '[COLOR="DimGray"]+ [/COLOR][COLOR="Black"]@TableID[/COLOR] [COLOR="dimgray"]+[/COLOR]';'[/COLOR]

 

[Tupacmoche]

Still getting error message:

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 207, Level 16, State 1, Line 14
Invalid column name 'EN'.
(1 row(s) affected)
Msg 207, Level 16, State 1, Line 14
Invalid column name 'EH'.
(1 row(s) affected)
Msg 207, Level 16, State 1, Line 14
Invalid column name 'ESH'.
(1 row(s) affected)
Msg 207, Level 16, State 1, Line 14
Invalid column name 'ERH'.
(1 row(s) affected)

Copied code exactly.

 

[Minty]

Hmm but that's not the same error.

Try this then- and I tested it this time.

SET @SQL = 'Update MRN_Processing 
SET SourceIs = (Case When e.Match = ''Y'' then 1 else 0 end), 
TargetIs = (Case When n.Match = ''Y'' then 1 else 0 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
Where tbl_id = '''+ @TableID +''' ;'

 

[Tupacmoche]

Super that works!

 

[Tupacmoche]

Hi Minty,

I have expanded the previous code and am having a similar problem with terminating statements in the @sql script section. Would you please tell me what, I'm now 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 ';'.

 

[WayneRyan]

Tupamoche,

It's been a while, but ...

It never finished the Delete Statement (trailing parenthesis).

SET @SQL = 'Delete ' + @TableName + ' 
Where MRN IN 
(Select SourcePatientMRN
from MRN_Processing 
Where Processing_code = 2 and Tbl_ID = ''' + @TableID + '''[U][SIZE="4"])[/SIZE][/U]

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 + ''';'
[\code]

Wayne