Variable in where clause not working (1 Viewer)

Tupacmoche

Registered User.
Local time
Yesterday, 20:41
Joined
Apr 28, 2008
Messages
291
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?:mad:


 

Minty

AWF VIP
Local time
Today, 00:41
Joined
Jul 26, 2013
Messages
10,355
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

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

AWF VIP
Local time
Today, 00:41
Joined
Jul 26, 2013
Messages
10,355
Weird , I've never used that syntax to set a variable in SQL.

Any ways - I think your issue is a missing '

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

The pretties helped me spot it
 

Minty

AWF VIP
Local time
Today, 00:41
Joined
Jul 26, 2013
Messages
10,355
It should read like this - the string concatenation of the tableID was out
Code:
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

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

AWF VIP
Local time
Today, 00:41
Joined
Jul 26, 2013
Messages
10,355
Hmm but that's not the same error.

Try this then- and I tested it this time. ;)
PHP:
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

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

AWF VIP
Local time
Today, 00:41
Joined
Nov 19, 2002
Messages
7,122
Tupamoche,

It's been a while, but ...

It never finished the Delete Statement (trailing parenthesis).

Code:
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
 

Users who are viewing this thread

Top Bottom