Solved

Using temporary variables in a query

Posted on 2006-10-29
16
4,243 Views
Last Modified: 2012-05-05
I'm running a commercial app named ProLaw.  This application is running on top of a Microsoft SQL Server 2000 database.  This application allows a customized report function.  The way the report function works is a query is performed, then a report is called which runs against the results of the query.

The report design part of the program allows 'simple' variables to be dragged onto the body of the report from a list of variables in the database.  For more complicated items it allows a query to be performed.  The function used to invoke a query is select('Real SQL query').  The real SQL query is in standard notation with a few exceptions.  I could give much more detail on the report function, but I don't think it is relevant to the question.

The report on which I am working has several 'simple' variable fields and also some fields that require running a query.  Several of the queries use the SUM function.  Here's what I'm trying to find out if I can do.  I'd like to store the results of some of the sum functions to use later on in the report.  The reason that I want to do this is that even one of the sum queries is long.  If I can't store the result in a temporary variable, then to do arithmetic later on using that sum, I will have to repeat the query, plus the other ones involved in the arithmetic.  For instance, to find a total for the report I will have set a field equal to:
select('Query1') + select('Query2') - select('Query3') - select('Query4'), etc.  I want to make it clear that I can't set a variable called Query1, but rather where I have this in the example I would have to put the full queries.

The problem is that each of these Queries is (what I consider) very lengthy.  I'll give an example below.  I want to avoid doing this if possible because editing and debugging these will be very difficult.   The report functionality within ProLaw does not directly allow this,  but I'd like to know if there is a some functionality within SQL to store the results of these queries to temporary variable that I can use later in the report.

Constraints:  Because this is commercial software under a support contract, there are several things that I can't do:
1) I can't make any changes to any of the existing tables or create new ones in the database.
2) I can't use the update function
3) I can't do anything like a stored procedure, etc.
4) There are probably other constraints, but I can't say in advance what they might be.

So in a nutshell what I'm looking for is a way to assign a temporary variable in a query and be able to use it later in the report.  In order to use it later I would have to use it inside some type of query so that I can invoke the SQL functionality to be able to retrieve the value of the variable.

Here is an example of one of the queries.  This was provided to us by ProLaw support.  The actual report will have at least a dozen queries like this.  To get the 'grand total' at the end of the report I would have to have a set of queries about 6 times longer than this.

select('select Sum(QTOTALBILLMP1) as frog from matters, mattersqpimedprov, mattersqpimedprov1 where mattersqpimedprov1.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^  ') + select('select Sum(QTOTALBILLEMS1) as frog2 from matters, mattersqpimedprov, mattersqpimedprov2 where mattersqpimedprov2.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^  ') + select('select Sum(QTOTALBILLHOSP1) as frog3 from matters, mattersqpimedprov, mattersqpimedprov3 where mattersqpimedprov3.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^  ')
0
Comment
Question by:frank_wwww
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 29

Expert Comment

by:Gautham Janardhan
Comment Utility

1) Create a stored procedure
2) build ur query string according to ur need
3) use sp_executesql

eg:

DECLARE @testint INT
DECLARE @String NVARCHAR(500)

SET @testint = 35
SET @String = N'SELECT * FROM ur table WHERE col1 = ' +
                 CAST(@testint AS NVARCHAR(10))
EXEC(@String )

0
 

Author Comment

by:frank_wwww
Comment Utility
Thanks for your suggestion.  As I said above, one of my constraints is that I can't create a stored procedure without potentially voiding the support contract.

The only direct SQL function that I can invoke from the report writer function is the SELECT function.  Whatever answer that I will ultimately use must be contained in a SELECT statement.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
Comment Utility
yes u can do it. within the query u write u can create a #table

for example

string str = " CREATE TABLE #MyTempTable (col1 varchar(10), name varchar(50)) "
                 + " GO "
                  + " INSERT INTO #MyTempTable select * from atest "

                  + " select * from #MyTempTable"
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
Comment Utility
another way is (in C#)


string str =  " select * into #MyTempTable from atest;select * from #MyTempTable;drop table #MyTempTable ";
adapter.selectcommand = new sqlcommand (str);
adapter.fill(MyTable);
0
 
LVL 10

Assisted Solution

by:AaronAbend
AaronAbend earned 100 total points
Comment Utility
"Because this is commercial software under a support contract"

As owner of a software company I can say that it would have to be a fairly unusual contract clause to say that you cannot create a stored procedure in the database. So before you spend a lot of time and money avoiding a problem that may not exist - let's find out. If you want, I will send an email to prolaw right now and will get an answer on your behalf (as a software contractor, I have clients who could use this product and I, as a contractor, would naturally not purchase a product if my custom work with stored procedures would invalidate my client's support contract).

Keep in mind that the last people to know what is in a software contract are the tech support people you work with. They are told to keep you from making changes and to scare you away from doing anything that might increase the number of support calls. But that is not a legal issue and believe me, they are far more interested in keeping you on the incredibly lucrative support contract you already have than whether you create a stored procedure. Yea - they will threaten and stuff - but don't believe a word of it. If you had any idea how valuable a customer on support is, you would laugh at the suggestion that you would "lose" your support. I know they are part of a huge company, but it doesn't matter - there are people who care about your patronage and would certainly  not blow it because you are adding stored procedures.

Now the only possible exception is if you are trying to avoid buying a product they sell.. that could be an issue. Then there may be cause for pause. Let me know if you want to explore this route.

Unless your c# is adequate, in which case I would go that way as described by gauthampj since you could build some nice stuff that way (and maintain it of course)...
0
 

Author Comment

by:frank_wwww
Comment Utility
gauthampj,

I was able to test what you suggested (sort of) and I was able to create a table.  I am a little confused by the example that you gave so I'll tell you what I did and the results I got.  Even though I was able to create the table, and I understand in theory that I can store these values in a table and then retrieve them later, I don't see how I could actually use that to do what i want.  I'm sure that you can explain that, it's just that I don't understand right now how to do it.  My SQL experience was with Oracle about 15 years ago, and although I don't mind researching things (and in fact I spent a couple of hours looking up temporary tables and related items after I saw your post) I could use quite a bit of hand-holding.

In your sample code you have the statement 'select * from atest'.  I assume that this is either where I would insert my own select statement, or else it is a bogus select statement just to let you create the table.  I would appreciate it if you would explain this part in more detail.

Prolaw lets you enter calculations to use in the report fields.  Part of the window where you enter the calculations lets you test the calculation to see if it produces the results you are looking for.  All of the results below came were from my typing in a calculation(query) and testing it.  Here's what happened as I tested what you suggested.

First I took the string you gave above and created a single concatenated string.  The expression that I evaluated was:

select('CREATE TABLE #MyTempTable (col1 varchar(10), name varchar(50))  GO INSERT INTO #MyTempTable select * from atest select * from #MyTempTable')

I ran the query tester and it replied:

Warning:  Incorrect syntax near the keyword 'Insert'

I ran the query a second time and got the same error.  I assumed that it was the word GO that was causing the problem, so I removed it and had:

select('CREATE TABLE #MyTempTable (col1 varchar(10), name varchar(50))  INSERT INTO #MyTempTable select * from atest select * from #MyTempTable')

When I tested this I got the error message:

Warning:  Incorrect object name 'atest'

I ran the same query a second time and I got:

Warning:  There is already an object named #MyTempTable in the database

Which means that I was able to create a table!!

Since it had objected to the atest part I removed that and had:

select('CREATE TABLE #MyTempTable (col1 varchar(10), name varchar(50))  INSERT INTO #MyTempTable select * from #MyTempTable')

When I ran the tester I got the error message:

Warning:   CommandText does not return a result set

It's great that I can create a table.  I don't have a clue about how to use this table within a select statement or series of select statements.  I assume that would use some type of insert.

Because I don't know how to express this in SQL I'll show you what I want to do using some pseudoCode.

I want to store a value into a temporary value.  For example I'd like to do something like:

tempVar1 = select sum(totalMedicalExpenses) where ...

Then later I would need to retrieve the value of tempVar1 and other temporary values to create something that would show total expenses something like:

tempVar1 + tempVar 2 + tempVar3

This would need to be returned as the result of a select statement.  I can sort of envision how this would happen.
Assuming that I could store some temporary values into the table then here's how I would try to get the values back out.

First I would assume that I somehow stored the following values into #MyTempTable
#MyTempTable.name = tempVar1 , #MyTempTable.col1 = 1234
#MyTempTable.name = tempVar2 , #MyTempTable.col1 = 4567
#MyTempTable.name = tempVar3 , #MyTempTable.col1 = 8901

Assuming that later in the report I wanted to get the total of these three fields I could do something like
select('select col1 from #MyTempTable where name = 'tempVar1') +
select('select col1 from #MyTempTable where name = 'tempVar2') +
select('select col1 from #MyTempTable where name = 'tempVar3')
I'm sure there's a way to do this from within a single select statement, but I don't know how.  I realize that in the example table that you gave that col1 is a character field, but I assume that I would instead use a numeric field for this.

I assume that I should delete this table when I'm through with this report.  I read that the temporary tables get automatically deleted, but I'm unclear exactly when that happens.

I don't think I can use the c# because I have to operate from within the report writer function of proLaw.  If there is some way that I can, please explain it to me.

Thanks for your help so far.  
0
 

Author Comment

by:frank_wwww
Comment Utility
AaronAbend

I will check with ProLaw support on exactly what I can or can not do.

I am not trying to avoid buying a product that they sell, but they do sell consulting services, so that may make them more protective of the database.  I've worked many years in the support end of the business and I can completely understand why they want to be protective of the database and basically make sure that everything you do to it is done through their application instead of through raw SQL.

I haven't seen the support contract.  I am a consultant for the law office that is using ProLaw.  My inclination is that even if this is not explicitly forbidden in the contract, if ProLaw support says don't do it, then I won't do it.

I want to say a good word about the support folks at ProLaw.  I've been in the computer industry for 25 years and during that time I've held corporate jobs where I had access to the top-tier support at several of the major software companines.  The folks at ProLaw support are the most competent support people that I have ever dealt with.  I've talked with 5 or 6 of their support people.  They are very very sharp in what they know about their product.  One of the people was somewhat of a jerk, but still extremely smart about his product.  Besides the one who was a jerk, all the others were amazingly smart, and very adept at solving problems with their software.  They are truly a pleasure to deal with.

I'm going to contact them and ask them about stored procedures, temporary tables, and creating new tables within the database, and as soon as I hear back from them I'll post it here.

thanks for your suggestions
0
 

Author Comment

by:frank_wwww
Comment Utility
I asked ProLaw support about doing the following items:

1) Creating a temporary table within the database.

2) Creating a stored procedure.

3) Using an insert statement within a query to store values into the temporary table.

4) Creating a table within the database.

Their reply was that all of those items alter the database, and that any time I altered the database I need to have them approve anything that I do, or I risk voiding the support contract.

I will, however, award points based on a complete solution within SQL select statements, even if I can't actually do it within ProLaw's constrainsts.  Of course if someone can offer me a solution that does not involve any of the actions that ProLaw constrains that would be my preferred method.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You are forcing SQL to do essentially the same query over and over again.  You can get many totals at once by:

select('select Sum(QTOTALBILLMP1) +
                    Sum(QTOTALBILLEMS1) +
                    Sum(QTOTALBILLHOSP1) as frog
from matters, mattersqpimedprov, mattersqpimedprov1 where mattersqpimedprov1.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^  ')
0
 

Author Comment

by:frank_wwww
Comment Utility
Thanks Scott.   One thing I noticed about your suggestion is that you only use mattersqpimedprov1.

The query that I posted contains:

select('select Sum(QTOTALBILLMP1) as frog from matters, mattersqpimedprov, mattersqpimedprov1 where
....
select('select Sum(QTOTALBILLEMS1) as frog2 from matters, mattersqpimedprov, mattersqpimedprov2 where
....
select('select Sum(QTOTALBILLHOSP1) as frog3 from matters, mattersqpimedprov, mattersqpimedprov3 where
....

So I'm using mattersqpimedprov1, 2, and 3.  The query that you suggest is only using mattersqpimedprov1.  I'm assuming that it has to use the 2 and 3 also or ProLaw support wouldn't have given it to us that way.  On the other hand, I'm more than willing to be persuaded if I can use your suggestion and only use mattersqpimedprov1.
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 400 total points
Comment Utility
Frank,
The Code snippet i gave u was just a sample to show u how it can be done.

<<First I would assume that I somehow stored the following values into #MyTempTable
#MyTempTable.name = tempVar1 , #MyTempTable.col1 = 1234
#MyTempTable.name = tempVar2 , #MyTempTable.col1 = 4567
#MyTempTable.name = tempVar3 , #MyTempTable.col1 = 8901
>>

in this u r storing data in rows in ur temp table
instead u create a table #MyTempTable (tempvar1 int, tempvar2 int, tempvar3) .. depending on how many cols u want

so ur table #MyTempTable will look like this
tempvar1    tempvar2     tempvar3
1234            4567           8901

If u run a query like this u will get sum

Select tempvar1+tempvar2+tempvar3 from #MyTempTable

so the steps to follow are,

First u create a #Temp table, with columns u need to store.

for example u create a temp table  

table name ---        #Temp table
cols ----   tempvar1, tempvar2, tempvar3 etc according to ur need.

Now insert the values into temp tables from ur other tables using select statement.

now u can manipulate ur select statement like this

select tempvar1+ tempvar2 from #Temp table

Hope this gives u a clear idea

0
 

Author Comment

by:frank_wwww
Comment Utility
Thanks  gauthampj,

I still have a couple more questions.

1)  I won't be able to have access to the values to set into the temporary table all at the same time, so I'm not sure how I would insert them into a row of the table.  It seems to me the easiest way to do this would be to do this is to create a temporary table for each temporary variable that I need to use.  I wouldn't need to worry about performance because it's a small database without too many users.

2)  Can you give me an example of how to do the insert.  Please use a real example using the following query.  

select Sum(QTOTALBILLMP1) as frog from matters, mattersqpimedprov, mattersqpimedprov1 where mattersqpimedprov1.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^

Please modify this statement with an insert statement that would show how to set the value of this select statement into one of the temporary tables, assuming that I had created the table earlier, or assuming creating the table in this statement, whichever you prefer.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
Comment Utility
insert into #TenmpTable
select * from (
select Sum(QTOTALBILLMP1) as frog from matters, mattersqpimedprov, mattersqpimedprov1 where mattersqpimedprov1.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^)) as col1tab join
(
select Sum(QTOTALBILLMP1) as frog from matters, mattersqpimedprov, mattersqpimedprov1 where mattersqpimedprov1.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^))   as col2tab on col2tab .matters=col1tab.matters  
and col2tab .matters= col3ab .matters

join
(
select Sum(QTOTALBILLMP1) as frog from matters, mattersqpimedprov, mattersqpimedprov1 where mattersqpimedprov1.mattersqpimedprov=mattersqpimedprov.mattersqpimedprov and mattersqpimedprov.matters=^'+MattersQPIMEDPROVQuery.Matters+'^ and matters.matters=^'+MattersQuery.Matters+'^))   as col3tab on col2tab .matters=col1tab.matters  
and col2tab .matters= col3ab .matters



0
 

Author Comment

by:frank_wwww
Comment Utility
Thanks to everyone for their input into this problem.  Since I risk voiding the support contract, I won't be able to put gauthampj's into use, but it is a viable solution to the problem.  I've increased the points from 400 to 500 because I want to give AaronAbend some points as well, but I feel that gauthampj deserves the 400 points that I offered for the question.

I want to thank gauthampj for patiently coming back and providing additional info.

I want to make a general rant about SQL.  I spent about 10 years as a C programmer.  I spent a couple of months doing Oracle about 15 years ago.  I find it very frustrating that SQL does not directly offer a temporary variable capability.  I realize that most programming of SQL would be wrapped into another language and would use variables from that language.  I further realize that SQL and database programming is conceptually very different from procedural languages like C, but still how hard would it be to have something like:

select Sum(myField) as frog persistent where ....

In this case frog would be a variable that I could use later on in other SQL statements instead of having to reuse these terribly long queries.
0
 
LVL 10

Expert Comment

by:AaronAbend
Comment Utility
Thanks for the points.  The vendor does not say you cannot alter the database, you just need their permission. I say - get it!

The reason SQL databases do not generally let you do these variable things is that at least half their design (or more) is for processing transactions. Data in memory could become invalid due to transactions, or it could require locks, all of which complicates that aspect of what a database is there for. SQL is just a data retrieval language. TSQL and PL/SQL (oracle's variant) were created to make some things easier to do in the database (mostly for maintaining referential integrity). But TSQL and SQL were never designed to replace programming langauges.

And what about views - they persist.  The constraints allegedly imposed by the vendor cannot be blamed on SQL. Just an opnion.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
D'OH, sorry, didn't look closely enough.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

763 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

10 Experts available now in Live!

Get 1:1 Help Now