Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 512 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 527 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 534 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 570 Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1244 Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1442 Strict Standards: Redefining already defined constructor for class wpdb in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/wp-db.php on line 306 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/cache.php on line 103 Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/cache.php on line 431 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/query.php on line 61 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/theme.php on line 1109 Strict Standards: Declaration of Walker_Comment::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Redefining already defined constructor for class WP_Dependencies in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/class.wp-dependencies.php on line 31 Strict Standards: Redefining already defined constructor for class WP_Http in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/http.php on line 61 SQL Tidbits » declare

You are currently browsing the archives for the declare tag.

The perils of not specifying a variable or parameter length

December 12th, 2008

When declaring a variable or parameter as varchar or nvarchar, you might get into some nasty surprises if you don’t specify the number of characters in the data. If you are declaring a variable like DECLARE @string AS VARCHAR, you are going to end up with a string that only holds 1 character. That’s probably not what you intended, but rather DECLARE @string AS VARCHAR(50) or similar.

The behaviour is similar when using CAST and CONVERT. Then, the default is to limit the data to 30 characters.

Some examples of this:

DECLARE @string AS VARCHAR
SET @string = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit'

SELECT
'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN('Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [varchar],
CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR) AS [Casted varchar],
LEN(CAST('Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS VARCHAR)) AS [Length of casted varchar]
GO

DECLARE @string AS NVARCHAR
SET @string = N'Lorem ipsum dolor sit amet, consectetur adipisicing elit'

SELECT
N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS [Original string],
LEN(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit') AS [Length of original string],
@string AS [nvarchar],
CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR) AS [Casted nvarchar],
LEN(CAST(N'Lorem ipsum dolor sit amet, consectetur adipisicing elit' AS NVARCHAR)) AS [Length of casted nvarchar]

Results:

Original string                                          Length of original string varchar Casted varchar                 Length of casted varchar
-------------------------------------------------------- ------------------------- ------- ------------------------------ ------------------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit 56                        L       Lorem ipsum dolor sit amet, co 30

(1 row(s) affected)

Original string                                          Length of original string nvarchar Casted nvarchar                Length of casted nvarchar
-------------------------------------------------------- ------------------------- -------- ------------------------------ -------------------------
Lorem ipsum dolor sit amet, consectetur adipisicing elit 56                        L        Lorem ipsum dolor sit amet, co 30

(1 row(s) affected)