?
Solved

Using Variables in SQL: Must declare scalar variable

Posted on 2013-02-01
15
Medium Priority
?
2,225 Views
Last Modified: 2013-02-02
What's wrong with this SQL?
I can't get it to run for anything, and most of the time (depending what I'm trying) it fails with Error 137, "Must declare the scalar variable "@sqlVAR1".

BEGIN TRY
	BEGIN TRANSACTION
		DECLARE @sqlVAR1 AS int;
		DECLARE @sqlVAR2 AS varchar(512);
		
		SET @sqlVAR2 = 'http://www.example.com/images/dynamically-assigned-via-ASP.jpg';
		SET @sqlVAR1 = (SELECT ISNULL(colImgID,0) FROM Database WHERE colImgURL = @sqlVAR2);
		
		IF NOT EXISTS ( SELECT 'true' FROM Database WHERE colImgID = @sqlVAR1 )
		BEGIN 
			INSERT INTO Database (colSourceID, colImgURL, colPageURL) 
			VALUES ('100', 
			'http://www.example.com/images/dynamically-assigned-via-ASP.jpg', 
			'http://www.example.com/source-page.asp'); 
			SET @sqlVAR1 = ( SELECT colImgID FROM Database WHERE colImgID = SCOPE_IDENTITY() ); 
		END
		UPDATE Database SET colPreviouslyUsed = 'true' WHERE colInCurrentUse = 'true';
		UPDATE Database SET colInCurrentUse = 'false';
		UPDATE Database SET colInCurrentUse = 'true' WHERE colImgID = @sqlVAR1;
		
		SET @sqlVAR1 = NULL; 
		SET @sqlVAR2 = NULL; 

	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	PRINT 'ERROR - Transaction generated the following error: ' + ERROR_MESSAGE(); 
	IF (@@TRANCOUNT > 0)
		ROLLBACK TRANSACTION
END CATCH

Open in new window


So, one thing, I'm very confident it's not the outer structure (the BEGIN TRY/BEGIN CATCH), because I grabbed that from another place where it's live and working.
If I try running just the innards of the TRANSACTION, it still fails; same error.

DECLARE @sqlVAR1 AS int;
DECLARE @sqlVAR2 AS varchar(512);

SET @sqlVAR2 = 'http://www.example.com/images/dynamically-assigned-via-ASP.jpg';
SET @sqlVAR1 = (SELECT ISNULL(colImgID,0) FROM Database WHERE colImgURL = @sqlVAR2);

IF NOT EXISTS ( SELECT 'true' FROM Database WHERE colImgID = @sqlVAR1 )
BEGIN 
	INSERT INTO Database (colSourceID, colImgURL, colPageURL) 
	VALUES ('100', 
	'http://www.example.com/images/dynamically-assigned-via-ASP.jpg', 
	'http://www.example.com/source-page.asp'); 
	SET @sqlVAR1 = ( SELECT colImgID FROM Database WHERE colImgID = SCOPE_IDENTITY() ); 
END
UPDATE Database SET colPreviouslyUsed = 'true' WHERE colInCurrentUse = 'true';
UPDATE Database SET colInCurrentUse = 'false';
UPDATE Database SET colInCurrentUse = 'true' WHERE colImgID = @sqlVAR1;

SET @sqlVAR1 = NULL; 
SET @sqlVAR2 = NULL; 

Open in new window


• Is it a variable scope issue? I'm not familiar enough with SQL to know.
• I've already dumbed this down from a more complex expression using CONTAINS.
• I've tried the UPDATE statements by themselves. They work (when replacing @sqlVAR1 with a specific number).
• If I remove the middle IF/BEGIN/END statement, the query pretends to work, but no rows are updated and some follow-up SELECT statements I've appended for testing don't run.

Thank you for looking it over.
0
Comment
Question by:universalglove
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +2
15 Comments
 

Author Comment

by:universalglove
ID: 38845819
I just simplified it even more, and it still doesn't work correctly.
Now it produces no error relating to the variables, of course, but it still doesn't hit the targeted image.
Maybe therein lies the source of my problem.

IF NOT EXISTS ( SELECT 'true' FROM Database WHERE colImgURL = 'http://www.example.com/images/dynamically-assigned-via-ASP.jpg' )
BEGIN 
	INSERT INTO Database (colSourceID, colImgURL, colPageURL) 
	VALUES ('100', 
	'http://www.example.com/images/dynamically-assigned-via-ASP.jpg', 
	'http://www.example.com/source-page.asp'); 
END
GO
UPDATE Database SET colPreviouslyUsed = 'true' WHERE colInCurrentUse = 'true';
UPDATE Database SET colInCurrentUse = 'false';
UPDATE Database SET colInCurrentUse = 'true' WHERE colImgURL = 'http://www.example.com/images/dynamically-assigned-via-ASP.jpg';

Open in new window

0
 
LVL 15

Expert Comment

by:tim_cs
ID: 38846662
Are you using the same test values everytime you run this?  If so then it's already in the database so it's going to look like nothing happened.  Does the output window show anything like...

(1 row(s) affected)?

Also I notice in your third example you have a GO after the end. If this is in the script with the variables that is what is causing it to break. Take the GO out.
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38846695
.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 43

Expert Comment

by:Eugene Z
ID: 38846874
try do not name object with the system word like "Database "


what update did not make ? 3rd ? withcolImgID = @sqlVAR1?

      UPDATE Database SET colPreviouslyUsed = 'true' WHERE colInCurrentUse = 'true';
            UPDATE Database SET colInCurrentUse = 'false';
            UPDATE Database SET colInCurrentUse = 'true' WHERE colImgID = @sqlVAR1;
--

try
do debug and set this line and you may will get the answer why it is not updated

select @sqlVAR1
UPDATE Database SET colInCurrentUse = 'true' WHERE colImgID = @sqlVAR1;


-- from your post ID: 38845819  - what do you mean it does not update ?
please check:
does this update block work?

UPDATE Database SET colPreviouslyUsed = 'true' WHERE colInCurrentUse = 'true';
UPDATE Database SET colInCurrentUse = 'false';
UPDATE Database SET colInCurrentUse = 'true' WHERE colImgURL = 'http://www.example.com/images/dynamically'
0
 
LVL 30

Accepted Solution

by:
hnasr earned 800 total points
ID: 38847048
I was unable to reproduce the issue.
The code seems to be fine.

Try checking the code line by line. And comment on the outcome..
0
 

Author Comment

by:universalglove
ID: 38847429
tim_cs:

I do keep using the same values for testing, but I know it's not updating. I have 2 records that I'm not targeting set to 'true' for colInCurrentUse, and those should get set to 'false' when it's all done correctly. They never change, and the one I am targeting never gets set to 'true'.
I don't use GO in the final script on the ASP pages, because that did seem to always break, as you're saying. But I'm running this for testing in an SQL client (SQuirreL SQL), and haven't noticed GO being a problem there yet; though I am still unclear as to the necessity of GO. I haven't found anything that explains its usage to my satisfaction - just sounds like it essentially commits the previous lines, so I've been using it as such until I can tell for sure whether it's helpful or not.
0
 

Author Comment

by:universalglove
ID: 38847490
EugeneZ:

No, I'm not using "database" in my actual script. I've just changed out object names w/ more generic, non-telling titles for the supplied example.

I guess one thing I didn't say, is that it seems to be indicating the problem is during the initial assignment to @sqlVAR2. I can't tell for sure, since it just says error at: listing that line and all subsequent lines.
However, where it says the problem is changes depending on which chunks I'm leaving in. But if I use a variable at all, it fails, apparently during the first variable assignment.
Given that it's so sporadic, that usually indicates to me that I'm doing something logically, insidiously improper that I shouldn't be doing.

To your last question: I did test the updates by themselves at one point, setting WHERE colImgURL = 'http://www.example.com/images/...jpg' directly w/o using a variable, and they were successful. Just retested it to be sure of myself. (Today's a different day, after all, and my hard drive (aka brain) gets wiped clean sometime each night.)
0
 

Author Comment

by:universalglove
ID: 38847502
I'll try more systematic testing now that I have more time; though I was trying to figure out yesterday how I might accomplish that when using variables.

EugeneZ again:

I'm not very clear what you mean by this statement:

try
do debug and set this line and you may will get the answer why it is not updated

select @sqlVAR1
UPDATE Database SET colInCurrentUse = 'true' WHERE colImgID = @sqlVAR1;

I will say that I'm not familiar at all with any debugging tools or methods for SQL (or much of any programming language). Haven't really gotten to that point in my learning yet. It might be what I spend a good chunk of time today doing, learning what might be available in my current SQL client or in SQL itself code-wise.

One further note:
I'm not super-confident my SQL client isn't a source of weirdness. Yesterday, it persistently gave me the "must declare scalar variable" error, until... I deleted the datatype assignments on the variables declarations (just seeing if I could). Didn't work, BUT! when I hit undo, and tried running the SQL, just to see, it ran. WTF? It didn't work, but it ran, and any subsequent run failed with the usual error. And I could repeat that. Delete the datatype assignment on the variable declaration, undo, SQL runs once saying it affected 1 row(s). OK, whatever.
0
 

Author Comment

by:universalglove
ID: 38847508
And (sorry, not trying to be verbose)...

To hnasr's comment, one thing I haven't tried is running the SQL from my actual ASP script, from the page itself. I was trying to be good and test out chunks individually before flinging them all up en masse to resolve all problems that way.

So, I haven't seen whether the script runs differently on the page or not. It won't run in my SQL client, so I was stopped there.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38847600
I was referring to testing on the server line by line or logical chunk of lines.
Example:
Declare @a as int;
declare @b as nvarchar(25)
set @a=1;
set @b='hello'
Select @a, @b ;

Result 1, hello - check passed

test next ....
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38847735
<I'm not very clear what you mean by this statement:>

added
these lines to debug your code -- run and see the clues

--check if @sqlVAR1 has a value

select @sqlVAR1

DECLARE @sqlVAR1 AS int;
DECLARE @sqlVAR2 AS varchar(512);

SET @sqlVAR2 = 'http://www.example.com/images/dynamically-assigned-via-ASP.jpg';
SET @sqlVAR1 = (SELECT ISNULL(colImgID,0) FROM Database WHERE colImgURL = @sqlVAR2);

IF NOT EXISTS ( SELECT 'true' FROM Database WHERE colImgID = @sqlVAR1 )
BEGIN 
	INSERT INTO Database (colSourceID, colImgURL, colPageURL) 
	VALUES ('100', 
	'http://www.example.com/images/dynamically-assigned-via-ASP.jpg', 
	'http://www.example.com/source-page.asp'); 
	SET @sqlVAR1 = ( SELECT colImgID FROM Database WHERE colImgID = SCOPE_IDENTITY() )
--check if @sqlVAR1 has a value 
select @sqlVAR1
END
UPDATE Database SET colPreviouslyUsed = 'true' WHERE colInCurrentUse = 'true';
UPDATE Database SET colInCurrentUse = 'false';
--check if @sqlVAR1 has a value 

select @sqlVAR1

UPDATE Database SET colInCurrentUse = 'true' WHERE colImgID = @sqlVAR1;

SET @sqlVAR1 = NULL; 
SET @sqlVAR2 = NULL;  

Open in new window

0
 

Author Comment

by:universalglove
ID: 38847737
OK.
Well, apparently, the answer is...

There is nothing wrong with the SQL.

It's the SQL client I'm using. It does not like variables for some reason.
Even trying to run something like this, no go:

DECLARE @sqlVAR2 AS varchar(512);

SET @sqlVAR2 = 'http://www.example.com/images/whatever_random_img.jpg';

SELECT @sqlVAR2;

Open in new window


I finally got onto the webserver and opened the copy of Visual Developer Studio 2005 and ran the above query there. No problem.
Pasted in the entire query in question to actually affect the database. No problem.

I'll have to look into what's up with SQuirreL SQL and variables. Guess this also pushes me towards learning Visual Web Developer all the more. (Was slated to happen, anyway.)
0
 

Author Closing Comment

by:universalglove
ID: 38847739
That is ultimately the answer, so hnasr gets it.
Hope nobody feels slighted. I appreciate everyone's input very much.
0
 

Author Comment

by:universalglove
ID: 38847778
EugeneZ:

I see what you mean. That's what can function the same as writing variables out to the screen in other languages. Gotcha.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38847855
Thanks for the added information.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question