RE: Auto truncate a string to be inserted to SQL table

From: Logicalman (tony9scott45us_at_com7cast.net34)
Date: 01/17/05 

Date: Mon, 17 Jan 2005 03:45:02 -0800

Thanks for clearing that up.

You therefore do have the two alternatives.

You may turn the warnings off, or you can truncate the string in the insert
statement.

For the latter, the following code can be used in a SProc, where @myFName is
the passed perameter you need to limit to 10 characters:

....
@myFName as varchar(100)='',
@myAge int = 0,
@myLName varchar(200)=''

INSERT INTO tblMyTable (myFName, myAge, myLName)
VALUES(LEFT(@myFName,10), @myAge, @myLName)

If you are not using a SProc, or any other way of controlling the input,
then the LEFT method must be assigned in the INSERT statement at the
application end. It is for this reason I use SProcs on the SQL side to allow
for Application-less coding (in other words, the SELECTS, INPUTS and DELETES
are all handled on the SQL server so should a business rule change (e.g. you
wish to allow 12 characters in the future) you can simply change it at one
place for all applications.

I agree with Tibor that turning the warnings off will suffice, but, and this
is only my opinion - that is not to say it is better for everyone - I tend to
code to avoid any warnings being produced at all, rather than ignoring them.

Again, you do have the choice, but I would urge you to be consistant in your
choices, and also make sure you add comments to the code - whichever choice
you make - to allow future programmers a chance to understand why you did
something a certain way.

Best wishes,

Tony

"BTLye" wrote:

> Sorry for the ambiguity...
>
> Actually what I want to do is truncate the string using any method at MSSQL
> server so that the INSERT action will not prompt me for any error.
>
> There is an application written by somebody else previously and being
> deployed to lot of client PCs. The app will insert record to SQL table at the
> end of the processing. I understand that it's faster to add the control
> (truncate the string) at the app. But making this will require all the client
> PCs to deploy the enhanced app again whereby in my case is not accepted by
> the users.
>
> So I wish to look for any alternative to do it at SQL server. The record
> should be inserted into the table by auto truncating any string which exceeds
> the max length being allowed by the table without giving any error. This is
> because for any error that prompts, the app will terminate and no record will
> be inserted.
>
> Hope that I've made my condition clear. Thanks for the guide so far :)
>
>
> "Logicalman" wrote:
>
> > BTLye,
> >
> > I'm not quite with what you are trying to do.
> > You have two choices to either turn off the warning and ignore them, or to
> > avoid the warnings being produced.
> > For the latter (my preference) dependant on how the application is sending
> > the INSERT instruction will depend on how you can proceed.
> > e.g. If you are calling a SProc from the application, and passing params,
> > one of which is the string causing problems, then you can handle this inside
> > the SProc ...
> >
> > ..(start of SProc) ...
> > @myVal nvarchar(100) = ''
> > AS
> > SET @myVal = LEFT(@myVal, 10)
> > ... (Rest of SProc) ...
> >
> >
> > If you are using a direct INSERT statement, then you will need to handle
> > this in the application itself. A point to note is that SProcs can, and do,
> > afford a little extra security to any application/SQL setup, ads they act as
> > middle-ware to ensure data integrity (as above) and also avoid any of the
> > underlaying table schema being visible to the Application side.
> >
> > Tony
> >
> >
> > "BTLye" wrote:
> >
> > > Thanks for the recommendation...But after rule is added...another error
> > > message is given...i might need to just truncate the string without halting
> > > the process...
> > >
> > > "Logicalman" wrote:
> > >
> > > > You will need to establish some rules for the truncation.
> > > >
> > > > The simplest one is to allow only the first 10 characters through. This is
> > > > attained by:
> > > > SELECT LEFT(myColName,10) FROM myTableName
> > > >
> > > > Others may be the right 10 characters (SELECT RIGHT(myColName,10) ..) etc..
> > > >
> > > > If you want a more complex algorithm, then I would suggest that INSERTS and
> > > > UPDATES to the table are handled vis a Stored Procedure, where your rules can
> > > > be effected at much greater depth.
> > > >
> > > > Tony
> > > >
> > > > "BTLye" wrote:
> > > >
> > > > > Hi SQL experts,
> > > > >
> > > > > May I know how can I auto truncate a string to be inserted to a SQL table to
> > > > > avoid from getting the error "String or binary data would be truncated."? For
> > > > > instance, if sent-in data has 15 chars but the column length is only set to
> > > > > 10, the data will be truncated for its last 5 chars.
> > > > >
> > > > > I understand that it is definately better to control this in the application
> > > > > which performs the record insertion. However, due to certain circumstances I
> > > > > might need to control this in SQL server.
> > > > >
> > > > > Any idea would be much appreciated. Thanks :)

Relevant Pages