Solved

Ho to check the Precision of a decimal field

Posted on 2013-06-24
1
465 Views
Last Modified: 2013-06-24
Hi there

I have a column, that is set to

decimal(18,0)

I need to check the (18,0) bit of it, and if it is (18,0) run some SQL else don't run it

so

if field

share is (18,0) do something
else
don't do anything

thanks
0
Comment
Question by:mousemat24
1 Comment
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
Comment Utility
To select column type and precision and scale:
select t.name as table_name, c.name as column_name, y.name as [type_name], c.precision, c.scale
  from sys.tables t
		inner join sys.columns c
	on t.[object_id] = c.[object_id]
		inner join sys.types y
	on c.system_type_id = y.system_type_id
 where t.name = 'your_table_name_here'
   and c.name = 'your_column_name_here'
--   and y.name = 'decimal'
--   and c.precision = 18
--   and c.scale = 0

Open in new window



To use in IF
IF ((select isnull(count(*), 0)
	  from sys.tables t
			inner join sys.columns c
		on t.[object_id] = c.[object_id]
			inner join sys.types y
		on c.system_type_id = y.system_type_id
	 where t.name = 'your_table_name_here'
	   and c.name = 'your_column_name_here'
	   and y.name = 'decimal'
	   and c.precision = 18
	   and c.scale = 0) = 0)
BEGIN
	Print 'Do something'
END
ELSE
BEGIN
	Print 'Do something else'
END

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

771 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

7 Experts available now in Live!

Get 1:1 Help Now