{"id":984,"date":"2012-05-27T09:41:34","date_gmt":"2012-05-27T13:41:34","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=984"},"modified":"2014-11-13T13:57:20","modified_gmt":"2014-11-13T18:57:20","slug":"print-vs-raiserror","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/","title":{"rendered":"PRINT vs. RAISERROR"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nDid you know that the RAISERROR<\/span> command can be used as a powerful PRINT<\/span> alternative?
\nIn this article I am going to compare the two and show the advantages that RAISERROR<\/span> offers over PRINT<\/span>.\n<\/p>\n

Print Lags<\/h3>\n

\nOne of the bigger disadvantages of PRINT<\/span> is its output buffering behavior. Let's look at an example to clarify what I mean by that:\n<\/p>\n

\n[sql]\nDECLARE @c INT;SET @c = 1;
\nWHILE(@c<100)
\nBEGIN
\n PRINT @c;
\n SET @c = @c + 1;
\n WAITFOR DELAY '00:00:00.2'
\nEND
\n[\/sql]\n<\/div>\n

\nThis loop prints out an increasing counter value, waiting for 0.2 seconds after each print. It runs for about 40 iterations before any output is generated. Then it waits for another ~40 iterations before the next block of output is returned to the client, and so on. You can observe this behavior in the following video:\n<\/p>\n

\n\"PRINT<\/a>\n<\/p>\n

\nSQL Server does not allow for any interactivity within batches, so printing is the only feedback mechanism available. In almost all cases it is used to inform about the current state of the execution which might even include warnings. This feedback mechanism is of reduced value, if you can't rely on it happening in real-time.\n<\/p>\n

Real-Time RAISERROR<\/h3>\n

\nIn its standard form, RAISERROR<\/span> shows the same buffering behavior. However, there is a little known option NOWAIT<\/span> that causes the output buffer to get flushed immediately. That makes it usable for real-time feedback:\n <\/p>\n

\n\"RAISERROR<\/a>\n<\/p>\n

\nThe disadvantage of using RAISERROR<\/span> in this way is that it outputs an additional error information line with each call and that at least in Management Studio the output is displayed in red. This can make it hard to find the actual information in all the output clutter. But there is a way to get rid of that too.\n<\/p>\n

The Printing RAISERROR<\/h3>\n

\nWhen RAISERROR<\/span> is called with a severity between 1 and 9, the output loses its red color but it still contains that extra line, only this time after the printed message instead of before. If you however call RAISERROR<\/span> with a severity of 0 or 10 it behaves just like the print statement:\n<\/p>\n

\n[sql]\nDECLARE @c INT;SET @c = 1;
\nWHILE(@c<10)
\nBEGIN
\n RAISERROR('test',0,1)WITH NOWAIT;
\n SET @c = @c + 1;
\n WAITFOR DELAY '00:00:01'
\nEND
\n[\/sql]\n<\/div>\n

\nWhen you execute this code you get print behavior without print lag:\n<\/p>\n

\n\"RAISERROR<\/a>\n<\/p>\n

The curious 17<\/h3>\n

\nThere is actually one exception to the buffering rule that you need to be aware of: When RAISERROR<\/span> is executed with a severity of 17, the output is held in a separate output buffer and returned after the batch finishes execution, even if the NOWAIT<\/span> option is specified.\n<\/p>\n

\nTo show this behavior, the following code alternatingly calls RAISERROR<\/span> with a severity of 16 and 17.\n<\/p>\n

\n[sql]\nDECLARE @c INT;SET @c = 0;
\nWHILE(@c<10)
\nBEGIN
\n DECLARE @s INT;SET @s = 16+@c%2;
\n DECLARE @m NVARCHAR(MAX); SET @m = CAST(@c AS NVARCHAR(MAX));
\n RAISERROR(@m,@s,10)WITH NOWAIT;
\n SET @c = @c + 1;
\n WAITFOR DELAY '00:00:01'
\nEND
\n[\/sql]\n<\/div>\n

\nExecuting above code produces this output:\n<\/p>\n

\n\"RAISERROR<\/a>\n<\/p>\n

\nThe same behavior can also be observed when using a severity of 18.\n<\/p>\n

Shortcomings<\/h3>\n

\nNow there is one disadvantage that I don't want to hide. While you can pass a variable to RAISERROR<\/span> you cannot execute any calculations within its arguments. That includes something as simple as casting an integer to a string. PRINT<\/span> on the other hand is able to take anything you throw at it:\n<\/p>\n

\n\"RAISERROR<\/a>\n<\/p>\n

\nTo alleviate this a little, RAISERROR<\/span> allows to use the C-style prinf<\/span> syntax:\n<\/p>\n

\n[sql]\nDECLARE @int INT;SET @int = -1-POWER(-2,31);
\nDECLARE @bigint BIGINT;SET @bigint = -1-POWER(CAST(-2 AS BIGINT),63);
\nDECLARE @nvarchar NVARCHAR(MAX);
\nSET @nvarchar = '{This is a string in braces!}';<\/p>\n

RAISERROR('INT:%d, BIGINT:%I64d, string:%s',0,1,@int,@bigint,@nvarchar);
\n[\/sql]\n<\/p><\/div>\n

\nExecuting the above batch produces this output:\n<\/p>\n

\n\"RAISERROR<\/a>\n<\/p>\n

\nThis does not cover all cases, though. Printing out a timestamp using the SYSDATETIME()<\/span> function for example requires you to cast the DATETIME2<\/span> value to a string. That cannot be done inside the call to RAISERROR<\/span> so you have to store the result of that conversion in a VARCHAR<\/span> variable and then pass that to the RAISERROR<\/span> statement.\n<\/p>\n

\nBesides of that, this substitution syntax is actually quite powerful. It allows specifying a fixed length for each substitution value. You can set a precision for numbers and you can request that a number gets displayed as hex- or octal-value. Have a look at the BOL entry for details: RAISERROR (Transact-SQL)<\/a> .\n<\/p>\n

Conclusion<\/h3>\n

\nWhile RAISERROR<\/span> is not as flexible as PRINT<\/span> when looking at possible parameter values, it has the great advantage that you can use it to control output buffer behavior. That makes it the preferred choice when the output is used to give feedback about the state of the currently executing SQL batch.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\nThis article shows a way to use RAISERROR as a PRINT alternative. It also explains in which situations it might be the preferred choice.\n<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,23],"tags":[],"class_list":["post-984","post","type-post","status-publish","format-standard","hentry","category-general","category-t-sql-statements"],"acf":[],"yoast_head":"\nPRINT vs. RAISERROR - sqlity.net<\/title>\n<meta name=\"description\" content=\"This article shows a way to use RAISERROR as a PRINT alternative. It also explains in which situations it might be the preferred choice.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PRINT vs. RAISERROR - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"This article shows a way to use RAISERROR as a PRINT alternative. It also explains in which situations it might be the preferred choice.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-05-27T13:41:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:57:20+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/05\/PRINT_with_buffering_delay.gif\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"PRINT vs. RAISERROR\",\"datePublished\":\"2012-05-27T13:41:34+00:00\",\"dateModified\":\"2014-11-13T18:57:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/\"},\"wordCount\":828,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/05\\\/PRINT_with_buffering_delay.gif\",\"articleSection\":[\"General\",\"T-SQL Statements\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/\",\"name\":\"PRINT vs. RAISERROR - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/05\\\/PRINT_with_buffering_delay.gif\",\"datePublished\":\"2012-05-27T13:41:34+00:00\",\"dateModified\":\"2014-11-13T18:57:20+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"This article shows a way to use RAISERROR as a PRINT alternative. It also explains in which situations it might be the preferred choice.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/05\\\/PRINT_with_buffering_delay.gif\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/05\\\/PRINT_with_buffering_delay.gif\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/984\\\/print-vs-raiserror\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PRINT vs. RAISERROR\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PRINT vs. RAISERROR - sqlity.net","description":"This article shows a way to use RAISERROR as a PRINT alternative. It also explains in which situations it might be the preferred choice.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/","og_locale":"en_US","og_type":"article","og_title":"PRINT vs. RAISERROR - sqlity.net","og_description":"This article shows a way to use RAISERROR as a PRINT alternative. It also explains in which situations it might be the preferred choice.","og_url":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-05-27T13:41:34+00:00","article_modified_time":"2014-11-13T18:57:20+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/05\/PRINT_with_buffering_delay.gif","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"PRINT vs. RAISERROR","datePublished":"2012-05-27T13:41:34+00:00","dateModified":"2014-11-13T18:57:20+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/"},"wordCount":828,"commentCount":1,"image":{"@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/05\/PRINT_with_buffering_delay.gif","articleSection":["General","T-SQL Statements"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/","url":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/","name":"PRINT vs. RAISERROR - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/05\/PRINT_with_buffering_delay.gif","datePublished":"2012-05-27T13:41:34+00:00","dateModified":"2014-11-13T18:57:20+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"This article shows a way to use RAISERROR as a PRINT alternative. It also explains in which situations it might be the preferred choice.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/05\/PRINT_with_buffering_delay.gif","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/05\/PRINT_with_buffering_delay.gif"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/984\/print-vs-raiserror\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"PRINT vs. RAISERROR"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-fS","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/984","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=984"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/984\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=984"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=984"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=984"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}