Solved

Using Variables in SQL: Must declare scalar variable

Posted on 2013-02-01
15
1,996 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
  • 8
  • 3
  • 2
  • +2
15 Comments
 

Author Comment

by:universalglove
Comment Utility
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
Comment Utility
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
Comment Utility
.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:universalglove
Comment Utility
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
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:EugeneZ
Comment Utility
<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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the added information.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now