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

I tried with the following code its working but when i use insied a stored procedure its not working.. How can achieve this one  inside the stored procedure ?


CREATE TYPE phkTest from decimal(15,3)
GO


DECLARE @Foo phkTest

SET @Foo = 1
PRINT @Foo

SET @Foo = 1.1
PRINT @Foo

SET @Foo = 1.12
PRINT @Foo

SET @Foo = 1.123
PRINT @Foo
Gowtham RamamoorthyAsked:
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.

Najam UddinCommented:
Try creating type outside stored procedure.
Brian CroweDatabase AdministratorCommented:
The type must be created outside the scope of the stored procedure otherwise it cannot compile it.

Can you expand a little bit on what you are trying to accomplish and maybe we can help you find an alternative.
Gowtham RamamoorthyAuthor Commented:
Hi Brian/Najam,

I have a  SP which start and End as shown below... I have various variable declarations where I have few 10's of varibles declared as a  decimal(15,3) which might change in the future also. So I created the global user defined type variable where in future if is want to change the variable to Decimal(15,2) it gets chnaged to all the defined variables.

As per your comment where should I exactly declare the user defined variables ?

Begin
Declaration part
{
Declare @variable Decimal(15,3).....
}
Code execution
Loop Ending
End
Brian CroweDatabase AdministratorCommented:
The user-defined datatype is persistent within the database in which it is created so you only need to create it once.  If you want to change it you would need to drop it and recreate it for instance if you wanted to change phkText from DECIMAL(15,3) to DECIMAL(15,2).  Alternatively you could just have multiple datatypes defined and just switch your code i.e. phkTest_Dec_15_3 and phkTest_Dec_15_2.  Once it is defined you can declare a variable of that type and pass it around just like you would any other.

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.