How to Declare and Use the MySQL DECIMAL Data Type

How to declare and use MySQL DECIMAL

What is a MySQL DECIMAL?  Simply put, it is just a number that can have a decimal in it (2012.56 and -2013.0034 are examples).  Let’s take a look at how to declare a MySQL DECIMAL.

DECIMAL(M, D)

M is the maximum number of digits (IE precision) and has a range from 1 to 65 (Take note that versions of MySQL prior to 5.0.3 allowed the range from 1 to 254).  This is the total number of digits INCLUDING decimal digits.

D is the number of digits to the right of the decimal point (IE scale) and has a range from 0 to 30 AND cannot be larger than M.

Why use a MySQL DECIMAL?

You would generally use a decimal type when you need to store exact fractional values like money.  It doesn’t suffer from the rounding errors of other number types in MySQL.

How do you use MySQL DECIMAL?

Here is some example mysql code…

CREATE DATABASE IF NOT EXISTS Testing;
USE Testing;
CREATE TABLE WeightCalculation (WeightCalculationID INT NOT NULL, Weight DECIMAL(10,5) NOT NULL);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (1,54.3445);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (2,928.23017);
SET @a = (SELECT SUM(WEIGHT) FROM WeightCalculation);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (3,@a);
SELECT * FROM WeightCalculation;
DROP DATABASE Testing;

Results

WeightCalculationID|Weight
1	54.34450
2	928.23017
3	982.57467

So let walk through this code and see what is going on.  To start, I create our Testing database if it doesn’t exist.  Then I create a table called ‘WeightCalculation’ and give it two columns ‘WeightCalculationID’ and ‘Weight’ in order to store some simple data.  Notice that column ‘Weight’ has been declare a DECIMAL of total length 10 digits and up to 5 decimal places.  Next, I insert two rows of data into the table.  Just for fun I calculate the sum of all the Weight column values and store them in a user-defined variable named @a then insert the sum into another row of the table.  Lastly, I perform a SELECT operation on the table to get it’s contents.  To clean up I drop the Testing database.

For further information check here.  Check here for more examples of precision math.

How to declare and use MySQL DECIMAL
How to declare and use MySQL DECIMAL

How to Create and Use a Dynamic Column in a Temporary Table

How to create and use a dynamic column in a temporary table T-SQL - ExampleSQL.com

In this post I’m going to describe how to create and use a dynamic column in a temporary table.  Let’s get started.

[Warning: dynamic sql is used here so use this tip with the appropriate security considerations]

Lets say you need to create a temporary table in which one of the column’s length must be dynamic.

Example
You’d like the size of column in a temp table to be change based on some condition such as declare the width of a column to be the same size as the length of another column or data (possibly from a different table or concatenations or a million other things).

Naive First attempt…

DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')';
DECLARE @dynamicSQL NVARCHAR(MAX) = 'CREATE TABLE #dynTestTable ( pk int,' + @dynamicColumn + ' )';

-- DEBUG
SELECT @dynamicLength AS dynamicLength,
@dynamicColumn AS dynamicColumn,
@dynamicSQL AS dynamicSQL ;

EXECUTE ( @dynamicSQL ) ;

INSERT INTO #dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;

After executing…

Msg 208, Level 16, State 0, Line 14
Invalid object name '#dynTestTable'.

The reason for this error is because when EXECUTE runs, it literally creates the temp table in a different session and returns back to the current code path with no change to the local environment. Thus, we are unable to do anything on our #dynTestTable.

One viable alternative is to use global tables…

-- be sure to delete the temp table if it exists first
DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')' ;
DECLARE @dynamicSQL NVARCHAR(MAX) = 'CREATE TABLE ##dynTestTable ( pk int,' + @dynamicColumn + ' )' ;

EXECUTE ( @dynamicSQL ) ;

INSERT INTO ##dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;

SELECT *
FROM ##dynTestTable ;

Results…

(2 row(s) affected)
pk          dynamicColumn
----------- -------------
1           abcde
2           bcdef

(2 row(s) affected)

The global temp table will work…so long as nobody else is doing anything with it (like dropping it). If you’re absolutely sure nothing else will be mucking with your global table then this could work. Another, safer, alternative is to declare the temp table first then alter it with dynamic sql. This gives you exclusive access to the table and no other process can meddle it up while you’re working with it.

IF OBJECT_ID('tempdb.dbo.#dynTestTable') is not null
DROP TABLE #dynTestTable;

CREATE TABLE #dynTestTable ( pk INT ) ;

DECLARE @dynamicLength int = LEN('123' + '45') ;
DECLARE @dynamicColumn NVARCHAR(MAX) = 'dynamicColumn CHAR(' + CAST(@dynamicLength AS NVARCHAR) + ')' ;
DECLARE @dynamicSQL NVARCHAR(MAX) = 'ALTER TABLE #dynTestTable ADD ' + @dynamicColumn + ';' ;

-- DEBUG
SELECT @dynamicLength AS dynamicLength,
@dynamicColumn AS dynamicColumn,
@dynamicSQL AS dynamicSQL ;

EXECUTE ( @dynamicSQL ) ;

INSERT INTO #dynTestTable
VALUES ( 1, 'abcde' ),
( 2, 'bcdef' ) ;

SELECT *
FROM #dynTestTable ;

Results:

(2 row(s) affected)
pk          dynamicColumn
----------- -------------
1           abcde
2           bcdef

(2 row(s) affected)

There you have it.

[wpfilebase tag=list id=1 pagenav=1 /]

How to create and use a dynamic column in a temporary table - ExampleSQL.com
dynamic column temp table sql

How to Loop using SQL Server Cursor Fetch fast_forward – 2005, 2008, R2, 2012, 2014

how to loop using sql server cursor fetch fast_forward tsql t-sql

In this post, describing how to loop using SQL Server cursor fetch fast_forward, I’m going to give an example of looping through a temporary table using a cursor and call a stored procedure for each iteration. The focus of this post is on the fast_forward cursor which is a shorthand way of saying the cursor is forward only and read only.  The fast_forward cursor type has performance optimizations enabled so this allows the speed to be about as good as possible.  The SQL Server query processing team wrote up a very technical and detailed article on how fast_forward cursors work and it can be found here.  It may come as a surprise that there are cursors that allow other actions as well.  I’ll be covering those in future posts.  On to the example…

The majority of code below is just wiring up code to run the example.  I create a stored procedure and temporary table which will be used by the cursor.  The cursor specific code is found on lines 55-83.

-- CURSOR EXAMPLES DATABASE PREPARATIONS

-- you should change this to whatever database you'd like to run
-- this example in.  I'm using AdventureWorks2008 since many folks
-- may already have it.  
USE AdventureWorks2008;

-- SAFEGUARD - check if the stored procedure exists and if so delete it
IF OBJECT_ID('[uspGiveRecommendationBasedOnFlag]') IS NOT NULL
	DROP PROCEDURE [uspGiveRecommendationBasedOnFlag];
GO

-- create the demo stored proc.  we'll delete it when we're done.		
CREATE PROCEDURE [uspGiveRecommendationBasedOnFlag]
	@articleFlag BIT,
	@articleName NVARCHAR(50)
AS
BEGIN
	-- you would do some actual work here in a real app
	IF(@articleFlag = 1)
		PRINT 'You should publish the article: ' + @articleName;
	ELSE	
		PRINT 'You shouldn''t publish the article: ' + @articleName;
END
GO

-- SAFEGUARD
-- not familiar with the .. syntax below?  check out this blog post 
IF OBJECT_ID('tempdb..#cursorExample') IS NOT NULL
BEGIN
    DROP TABLE #cursorExample
END

-- create the temp table
CREATE TABLE #cursorExample (
	id INT IDENTITY(1,1) NOT NULL,
	flag BIT NOT NULL,
	article_name NVARCHAR(100)
)

-- fill the temp table with sample data
INSERT INTO #cursorExample (flag, article_name)
VALUES 
(1,'cooking'),(1,'crafts'),(0,'flying'),
(1,'T-SQL'), (0,'wood carving'),(1,'gardening'),
(0,'reading'),(1,'beer brewing'),(0,'gaming');

-- create the variables we'll use when inside the the loop
DECLARE @articleFlag BIT;
DECLARE @articleName NVARCHAR(100);

------------------ CURSOR SPECIFIC CODE BEGIN --------------------
-- declare the fast_forward cursor.  notice we are essentially
-- using a SELECT query to build a list to iterate through
DECLARE myCursorSample CURSOR fast_forward FOR
SELECT flag, article_name FROM #cursorExample ;

-- now we have to open the cursor and tell it to go
-- acquire (i.e. FETCH) the NEXT (i.e. first since this is the first call)
-- item in the list
OPEN myCursorSample
FETCH NEXT FROM myCursorSample INTO @articleFlag, @articleName ;

WHILE @@FETCH_STATUS = 0 
    BEGIN       
        -- DEBUG
        -- PRINT ('Before stored proc execution cursor value: ' + @articleName ) ;	

		EXEC	[uspGiveRecommendationBasedOnFlag] 
				@articleFlag = @articleFlag,
				@articleName = @articleName;

		-- get the next article from the table using the cursor
        FETCH NEXT FROM myCursorSample INTO @articleFlag, @articleName;
    END

CLOSE myCursorSample ;
DEALLOCATE myCursorSample;

------------------ CURSOR SPECIFIC CODE END --------------------

-- CLEAN UP EXAMPLE 
DROP PROCEDURE [uspGiveRecommendationBasedOnFlag];

I tried to make the code as clear a possible with comments but I should point out a few things.  The SAFEGUARD comments are there only to show where I’m doing existence checks before actually performing an action on the database.  This is good practice for things like change scripts where running the same code twice shouldn’t cause errors or break something.  Second, this code is very well documented.  Although this amount of commenting is overboard for most production systems you should try to document why you’re using the cursor and perhaps your justification for doing so.  This should help minimize improper use of cursors in the codebase.  Lastly, while this example stored proc call isn’t doing anything a set based query couldn’t, you can imagine cases where based on values in a table you might have to carry out significant business logic actions which are not conducive to set based operations.

The results of the code execution are below.

(9 row(s) affected)
You should publish the article: cooking
You should publish the article: crafts
You shouldn't publish the article: flying
You should publish the article: T-SQL
You shouldn't publish the article: wood carving
You should publish the article: gardening
You shouldn't publish the article: reading
You should publish the article: beer brewing
You shouldn't publish the article: gaming

Valid for SQL Server 2005, 2008, R2, 2012, 2014.

how to loop using sql server cursor fetch fast_forward tsql t-sql
how to loop using sql server cursor fetch fast_forward tsql t-sql

dbo .. syntax and the default database

In this post I’m going to discuss sql server’s dbo .. syntax.  Truth be told, it isn’t rocket science.  ‘..’ can be used as a replacement for dbo when referring to the default schema in a tsql query.  If you need to access the default database frequenty, the dbo .. syntax shortcut can greatly increase your efficiency.  If you don’t use the dbo syntax very often then this may not help you too much but it does give you another way to brag to your programmer friends about how awesome you are.

In the following example I’ve made the assumption the default database is AdventureWorks2008.  Your default database is likely to be different.  To learn how to find the default database for a given login please see the post here.

SQL

SELECT * FROM AdventureWorks2008.dbo.AWBuildVersion; -- if the default schema is AdventureWorks2008 then -- the following is the same as the fully qualified -- table name given on line one. SELECT * FROM ..AWBuildVersion;

Output

SystemInformationID Database Version          VersionDate             ModifiedDate
------------------- ------------------------- ----------------------- -----------------------
1                   10.50.91013.00            2009-10-13 00:00:00.000 2009-10-13 00:00:00.000

(1 row(s) affected)

SystemInformationID Database Version          VersionDate             ModifiedDate
------------------- ------------------------- ----------------------- -----------------------
1                   10.50.91013.00            2009-10-13 00:00:00.000 2009-10-13 00:00:00.000

(1 row(s) affected)