How to Declare a global variable for storing the no.of .decimal values for a number(Variable)?

I need a global variable for storing the no.of Decimal values for a number (variable).... Below is the scenario...

I have a store Procedure where I declare all the decimal numbers at the start as shown below

Declare @timeID as Decimal(15,3), Declare @BusinessID as Decimal(15,3)

I need a variable which stores the decimal count say

Declare @decimalcount as Integer, @decimalcount=3

which I can use it in all the declartion i have done like

Declare @timeID as Decimal(15,@decimalcount)

Declare @BusinessID as Decimal(15,@decimalcount)

so by changing the value at one place would change all the values in the code.... Is this possible ?
Gowtham RamamoorthyAsked:
Who is Participating?
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.

x-menIT super heroCommented:
dynamic SQL:

DECLARE @decimalcount char(1) = 3
DECLARE @SQL nvarchar(MAX)

SET @SQL = 'Declare @BusinessID as Decimal(15,'+@decimalcount+'); set @BusinessID = 123.456789; SELECT @BusinessID'

exec(@SQL)
0
BitsqueezerCommented:
Hi,

dynamic SQL would not help here as the variable is only declared inside of the dynamic SQL part and would be lost after executing. So in the end you would need to convert the complete stored procedure to dynamic SQL which is not a good idea.

The easier way is to create a user-defined type like this:

CREATE TYPE dbo.BusinessID FROM decimal(15,3) NOT NULL; -- (or NULL instead)

Open in new window


Now you can use that as a variable everywhere:

DECLARE @BusinessID AS dbo.BusinessID;

Open in new window


Whenever you need to change that you only need to change the user-defined type.

Cheers,

Christian
1
ZberteocCommented:
The only way to emulate this is to declare a global temporary table inside a dynamic query and then use that instead of the variable:
declare
    @decimals varchar(2)=5
EXEC('IF OBJECT_ID(''tempdb..##var'') IS NOT NULL DROP TABLE ##var; CREATE TABLE ##var (val decimal(15,'+@decimals+'));')
insert ##var VALUES(2.12345678901234)
SELECT val from ##var

Open in new window

You will notice that when you insert the value it will be rounded up based on the next decimal value when >=5 but this is valid with variables too.
1

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.