Solved

Where would I declare variables in SQL Server 2008

Posted on 2012-04-13
9
309 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Declare variable at start of code bunch and use it in query.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Expert Comment

by:Yelnoc4
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

12 Experts available now in Live!

Get 1:1 Help Now