must declare variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

must declare variable

Author

Topic

toryee88
Starting Member

2 Posts

Posted - 2007-09-23 : 19:37:50

Hi,

I'm trying to run test queries in SQL Server 2000 Analyzer . My application requires the use of local variables.
I use transactions, in the transaction, I have something like

begin transaction;
declare @a_var int;
set @a_var = 12;
print @a_var;

If I run all these 4 statements with the Execute Query(F5), it runs to completion fine, @a_var prints out 12.
But if I run each statement individually with the Execute Query(F5), the 3rd statement returns error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@a_var'.

Why do I get this error? Is there a way to run each statement individually yet still let SQL Server think it's part of a transaction? I need each statement to be run individually because I use JDBC for my application.

Thank you for any help.

T.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-23 : 20:02:20

>> Is there a way to run each statement individually yet still let SQL Server think it's part of a transaction?
Well the transaction will be held until the end of the session or the commit so that's not a problem.
The variables are only declared for the batch - you are running them as separate batches so no go.

Your issue isn't the application - it's the way you are executing the sql.
You can execute it as a single statement and it will work.
Better to put it in an SP - will give you more control and it'll be easier/faster to develop.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

toryee88
Starting Member

2 Posts

Posted - 2007-09-23 : 20:41:11

My application uses JDBC to run transactions by running each statement of the transaction individually. I don't think I can put the code in a SP because the goal of my application is to interleave statements of two or more transactions to analyze behavior of concurrent transactions.
In other words, I need the statements to execute individually so that they can be interleaved with statements of another transaction.

Is there another work-around? Or can SP do what I want to do?

Thank you.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-24 : 07:00:35

There's no way of doing it if you want objects that have batch scope.
You can use a single row temp table for the variables
create table #a (a_var int null)
insert #a select null
begin transaction;
update #a set a_var = 12
declare @a_var int select @a_var = a_var from #a print @a_var

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources