Where would I declare variables in SQL Server 2008

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.
alexealdenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plummetCommented:
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
Éric MoreauSenior .Net ConsultantCommented:
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.
john MCommented:
the other thing also you could pass the parameter in if you calling it for a .net application
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Alpesh PatelAssistant ConsultantCommented:
Declare variable at start of code bunch and use it in query.
Yelnoc4Commented:
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).
plummetCommented:
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

alexealdenAuthor Commented:
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?
plummetCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alexealdenAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.