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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-07-2017, 07:34 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
Variable in where clause not working

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?



Tupacmoche is offline   Reply With Quote
Old 11-07-2017, 07:38 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,599
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: Variable in where clause not working

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?
__________________
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 offline   Reply With Quote
Old 11-07-2017, 07:46 AM   #3
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 157
Thanks: 40
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: Variable in where clause not working

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.

Tupacmoche is offline   Reply With Quote
Old 11-07-2017, 08:02 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,599
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: Variable in where clause not working

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
__________________
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 offline   Reply With Quote
Old 11-07-2017, 08:15 AM   #5
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 157
Thanks: 40
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: Variable in where clause not working

Where is it missing ?
Tupacmoche is offline   Reply With Quote
Old 11-07-2017, 08:25 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,599
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: Variable in where clause not working

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
Where tbl_id = '+ @TableID +';'
__________________
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 offline   Reply With Quote
Old 11-07-2017, 08:53 AM   #7
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 157
Thanks: 40
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: Variable in where clause not working

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.

Tupacmoche is offline   Reply With Quote
Old 11-07-2017, 09:22 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,599
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: Variable in where clause not working

Hmm but that's not the same error.

Try this then- and I tested it this time.
PHP 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
Where tbl_id = '''
+ @TableID +''' ;' 
__________________
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 offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tupacmoche (11-07-2017)
Old 11-07-2017, 09:43 AM   #9
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 157
Thanks: 40
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: Variable in where clause not working

Super that works!
Tupacmoche is offline   Reply With Quote
Old 11-08-2017, 10:17 AM   #10
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 157
Thanks: 40
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: Variable in where clause not working

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 ';'.
Tupacmoche is offline   Reply With Quote
Old 12-17-2017, 12:14 PM   #11
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,083
Thanks: 6
Thanked 55 Times in 53 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: Variable in where clause not working

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 + ''')

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

__________________
Pool Players Know All The Angles
WayneRyan is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable start of the month query, WHERE IF clause thraling Queries 4 05-31-2016 03:53 AM
Combobox as variable in where clause Broker666 Queries 10 03-18-2013 02:02 AM
Pass Where clause with a variable in RS joeKra Modules & VBA 9 06-06-2012 01:57 PM
Where clause is not working jchaturv Queries 5 11-02-2009 08:43 AM
Variable in where clause franc_5791 Forms 4 11-16-2008 01:45 PM




All times are GMT -8. The time now is 08:56 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