?
Solved

Where would I declare variables in SQL Server 2008

Posted on 2012-04-13
9
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 70

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 37842296
Declare variable at start of code bunch and use it in query.
0
 

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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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