Solved

Where would I declare variables in SQL Server 2008

Posted on 2012-04-13
9
312 Views
Last Modified: 2012-04-13
Hi,

I am up-scaling my database to SQL from Access. As I am converting many of the Queries to TSQL I am also looking at ways to make thing easier to maintain.

There are several variables that I would like to be available to me in queries and views, they really are very few and very simple. I would Like to simply store some exchange rates and also some tax rates.

So in my query I would be able to write something along the lines of

CAST(SUM(dbo.Products.[STG-R] * @TAXRATE) AS decimal(10 , 2))

rather than

CAST(SUM(dbo.Products.[STG-R] * 1.2) AS decimal(10 , 2))

I am confused as to where the best place to store something like this and how to declare it. Would it go in a stored procedure? Would it then be automatically available to all of my queries or would I need declare it at the beginning of the query.

Would appreciate any help you can offer. Many thanks.
0
Comment
Question by:alexealden
9 Comments
 
LVL 10

Expert Comment

by:plummet
ID: 37842146
I'd probably write a function to do this, so you might have a function called fnGetTaxRate which looked up the current value from a table and returned it to be used like this:

CAST(SUM(dbo.Products.[STG-R] * dbo.fnGetTaxRate) AS decimal(10 , 2))

If you'd like more info or an example I can probably dig one out.

Regards
John
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37842184
a function in a big query can decrease your performance. I would probably store the values in a table and join -OR- execute the query once, store the value in a variable and use the variable in the query.
0
 
LVL 3

Expert Comment

by:john M
ID: 37842277
the other thing also you could pass the parameter in if you calling it for a .net application
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 37842296
Declare variable at start of code bunch and use it in query.
0
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.

 

Expert Comment

by:Yelnoc4
ID: 37842339
You could take John's idea for a function and store it in a variable at the top of your proc:

declare @TaxRate VarTypeHere
set @TaxRate = dbo.fnGetTaxRate('TaxCodeHere')

Then reference the variable in the query.

The function would do a simple select on a lookup table (which you could do just as easily in the stored proc without a function call).
0
 
LVL 10

Expert Comment

by:plummet
ID: 37842442
Yes indeed, or the function could just be hardcoded to return the rate, eg:

Create function fnGetTaxRate()
returns numeric(4,2)
as
begin
	return 1.25
end

Open in new window

0
 

Author Comment

by:alexealden
ID: 37842594
OK The way I did it was by making a table called config. With 5 fields with the various rates etc in, just one row.

I am designing the queries as views so I have the visual option to include the config table. No join needed.

Incredibly I can now just use date from the one row columns as though it were a normal field, very cool and I only have one place to maintain the various rates..

not sure why this works really but luckily it does and a very simple solution.

Is there any drawback to this approach?
0
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
ID: 37843161
Hi Alex

If it works and it's not slowing anything down then it's good!

In terms of overhead I reckon it's about the same as other approaches discussed here, and as long as you stick to one row I think it will be fine. Personally I think it's a neater approach to wrap things like this up in functions, so you can call them once in a procedure and use the value from then on, but the main thing is that you have a working solution.

Regards
John
0
 

Author Comment

by:alexealden
ID: 37844366
Hi John,

Yes it seems to work fine, its for a very small office use so no speed issue. I no doubt will tackle the functions later on but this will be a good stop gap!

Many thanks

Alex
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

15 Experts available now in Live!

Get 1:1 Help Now