CODEWITHSUNDEEP
sqlsql-servert-sql
Kobojunkie
Kobojunkie

Reputation: 6545

SQL Server turn off ANSI_WARNING ON A stored procedure

I need to know how to turn off ANSI warnings on my stored procedure please. I keep getting the error

String or binary data would be truncated.

However, I would rather this be turned off so as I expect this and would rather allow it.

I added the statement 

SET ANSI_WARNINGS OFF 
GO

right before the stored procedure however, doing this does not seem to suppress the error at all.

For the reason why I have this truncate error to begin with, well one of my stored procs executes dynamic Sql to retrieve values(SQLFIddle showing the code ). And I had to set the length on all of my fields to the length of the max (NVarchar(3072)). When my query is executed however, I need them back to the right size when printing them to the client.

Would appreciate info on how to best deal with this please. Thanks in advance.

Upvotes: 1

Views: 17987

Answers (1)

EBarr
EBarr

Reputation: 12026

I agree with @marc_s -- fix the problem, not the symptom especially if your intent is to truncate. What will another developer think when he comes along and a proc is throwing these errors and a non standard flag was used to suppress the issue?

Code to make your intent to truncate clear.

Identifying your Problem
The fiddle doesn't display the behavior your describe. So I'm still a little confused as to the issue.

Also, your SQL fiddle is way too dense for a question like this. If I don't answer your question below work to isolate the problem to the simplest use case possible. Don't just dump 500 lines of your app into a window.

Note: The Max NVarchar is either 4000 in version of SQL 7 & 2000 or 2 Gigs (nvarchar(max)) in SQL 2005 and later. I have no idea where you came up with 3072.

My Test

If you're truncating at the SPROC parameter level, ANSI Warnings flags is ignored, as this MSDN page warns. If it's inside your procedure, I created a little test proc that displays the ANSI flag allowing truncation:

CREATE Proc DoSomething (@longThing varchar(50)) AS 

   DECLARE @T1 TABLE  ( shortThing VARCHAR(20) );

   SET ANSI_WARNINGS OFF 
   Print ' I don''t even whimpler when truncating'
   INSERT INTO @T1 (ShortThing) VALUES ( @longThing);

   SET ANSI_WARNINGS ON
   Print ' I yell when truncated'
   INSERT INTO @T1 (ShortThing) VALUES ( @longThing);

Then calling it the following works as expected:

exec DoSomething 'Text string longer than 20 characters'

FIXING THE PROBLEM

Nevertheless, why not just code so your intent to (potentially) truncate data is clear? You can avoid the warning rather than turn it off. I would do one of the following:

  • make your Procedure parameters long enough to accommodate the input
  • IF you need to shorten string data use Substring() to trim data.
  • Use CAST or CONVERT to format the data to your requirement. This page (section headed "Implicit Conversions" should help) details how cast & convert work.

My simple example above can be modified as follows to avoid the need to set any flag.

CREATE Proc DoSomethingBETTER (@longThing varchar(50)) AS 

   SET ANSI_WARNINGS ON  
   DECLARE @T1 TABLE  ( shortThing VARCHAR(20) );


   --try one of these 3 options...
   INSERT INTO @T1 (ShortThing) VALUES ( Convert(varchar(20), @longThing));
   INSERT INTO @T1 (ShortThing) VALUES ( Substring(@longThing, 1, 20));
   INSERT INTO @T1 (ShortThing) VALUES ( Cast(@longThing as varchar(20)) );

   Print('Ansi warnings can be on when truncating data');

An Aside - Clustered Guids

Looking at your fiddle I noticed that you Uniqueidentifer as the key in your Clustered indexes. In almost every scenario this is a pretty inefficient option. The randomness of GUIDs means your data is constantly being fragmented & re-shuffled.

Hopefully you can convert to int identity, you're using newsequentialid(), or COMB guids as described in Jimmy Nilsson's article. You can see more about the problem here, here, here, and here.

Upvotes: 4

Related Questions