Solved

Using Variables in SQL: Must declare scalar variable

Posted on 2013-02-01
15
2,111 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
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 42

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 200 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 42

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot table 2 45
sql update 2 39
SQL Get Store Procedure Column Name As Row 5 41
denied execute as 13 32
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…

730 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