asked on

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.

plummet

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 Moreau

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 M

the other thing also you could pass the parameter in if you calling it for a .net application

Alpesh Patel

Declare variable at start of code bunch and use it in query.

Yelnoc4

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).

plummet

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

alexealden

ASKER

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?

ASKER CERTIFIED SOLUTION

plummet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alexealden

ASKER

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