query tool that supports client side variables?

Question

query tool that supports client side variables?

kenambrose

SSCrazy

Points: 2513
More actions
December 26, 2007 at 11:13 am

#180086

My organization has a ton of legacy scripts that are poorly written such that they require repeated manual changes to literals and parameter values within each script each time they are run.
The message output from many of the statements must be reviewed between executions of different statements, so "go" is used to seperate statement blocks. As a result, we can't use @variables in one place at the beginning of each script, as the variable declarations go out of scope when statement blocks are separated by the "go" syntax.
Does anyone know of a RDBMS client query tool that supports the following:
1: support for client side named variables so that value substitution will occur on the client side prior to the sql statement transmission to the server for a named variable anywhere it is encountered in the code
2: automated history recording of sql statements executed from the client and the output results- both sql messages and data.
I can't believe all sql server clients don't support this. Oracle's OEM client supports this out of the box...
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

The best thing to do would be for your organization to hucker down and fix the poorly written code by turning them into viable stored procedures or views.

Than notwithstanding, what's wrong with letting your clients use Query Analyzer or a tool like AQT?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kenambrose SSCrazy Points: 2513 More actions · Answer 2

Hi Jeff,

Perhaps I wasn't clear, sorry. We do use query analyzer, and it does NOT support client side variable substitution (unless you want to count search and replace which is a big problem as it sometimes makes unintended changes).

Here's an example of the _problem_:

declare @mydate datetime

SET @mydate = getdate()

select * from mytable where mycolumn = @mydate

go

select * from another table where anothercolumn = @mydate

Note that the second sql statement will _bomb_. The "go" clause will create a new scope and the @mydate variable context is lost.

Here's what we want to do:

create a "global" variable in the client tool (ala DTS). Declare :mydate datetime = '1/1/2007'

select * from mytable where mycolumn = :mydate

go

select * from anothertable where anothercolumn = :mydate

Query analyzer definitely does not support the above approach. Does AQT? I looked and I don't think so?

Thanks,

Ken

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

Can you use a semi-colon to terminate a batch?

Your code will keep the variable in scope with a semi-colon.

My blog: http://jahaines.blogspot.com

kenambrose SSCrazy Points: 2513 More actions · Answer 4

Doesn't work because we run SQL statments manually to analyze results and sql messages.

This loses the variable context unless we continually repeat the @variable definitions and assignment of values.

This is so easy in Sql*PLUS it works perfectly:

DEFINE myvar = '1/1/2007'

select * from atable where acolumn = '&myvar'

go

select * from anothertable where anothercolumn = '&myvar'

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

Gotcha. There is no easy way to do this in SQL if you are running individual batches. I do not know of any other tool you can use to acheive the desired result.

As an aside, you can create a temp table to house the variable value and pull when needed.

My blog: http://jahaines.blogspot.com

kenambrose SSCrazy Points: 2513 More actions · Answer 6

Just as much hassle to repeatedly query a meta-data structure for the parameter values as it is to repeat the @variable definitions.

I am really surprised none of the third party query editors/client tools support this. For all the $$ they ask and all the chest-pounding they do, you'd think one of them would support such a useful feature, long available in Oracle query client...

ken

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

Ken,

Yeah, you're right... go breaks the stride of variables... AQT won't handle it. Still not sure why you need the "GO"'s. Yeah, they force an early display but why anyone really needs that is beyond me. Yep... I know you have a bunch of these that'd you'd have to rework... but I wouldn't let the clients do direct querying of a production box, anyway. Maybe on a reporting server.

Anyway, good luck. Sorry I don't have a solution for you other than "don't let 'em do that".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kenambrose SSCrazy Points: 2513 More actions · Answer 8

With or without the go's, we need to be able to run only some of the statements at a time, in order to manually review the output and possibly do some ad-hoc work, before continuing with the remaining statments.

Almost all the statements however use certain common parameters and/or literals, hence the desire to define and initialize the variables in one place in each script...

I think I did find one obscure query tool from Australia that supports this. I will do some testing and let you know. We are also looking for a client tool that logs all statements executed, and the output, so that complicates the search for a tool...

ken

kenambrose SSCrazy Points: 2513 More actions · Answer 9

I found it. It's also called Advanced Query Tool (confusing isn't it). Here's the product web site. It does client side variable defintion and substitution and some auditing capabilities...

http://www.querytool.com/