[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Using Variables in SQL: Must declare scalar variable

Posted on 2013-02-01
15
Medium Priority
?
2,294 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 31

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 31

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 31

Expert Comment

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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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 …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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