Solved

Parameter truncation doesn't raise error in stored proc

Posted on 2004-03-31
1
500 Views
Last Modified: 2008-02-01
I can't get my stored proc to return an error when the value passed to the parameter is larger than the parameter's definition and larger than the field definition.  If I execute the SQL in the query window, an error is returned.

Table definition:
CREATE TABLE Employee(Eid int, Fname varchar(20), Lname varchar(20))

Populate 1 record:
  INSERT INTO Employee (EId, FName, LName) VALUES (1,'Ford','Perfect')

Update it from Query Analyzer
  UPDATE Employee  
  SET fname = 'FName', Lname = 'LNamefjdkfjkdfjkdjfkdjfk'

This raises the following error:
  Server: Msg 8152, Level 16, State 9, Line 1
  String or binary data would be truncated.
  The statement has been terminated.

Here is the update statement converted into a stored proc:

CREATE PROC updateEmployee3 (
      @FName varchar(10)
      , @LName varchar(10))
AS  
  UPDATE Employee  
  SET  fname = @Fname, lname = @Lname

RETURN @@ERROR  
 
When I call the stored proc in query window as follows, the value for FName is truncated but no error is raised.  Why?
  exec updateEmployee3 'ABCDEFGHIJKLM', 'LName'

I've tried this with other types of errors, e.g., assigning NULL to a field defined as NOT NULL, assigning a string to a int field, and in those cases the appropriate error is returned.
0
Comment
Question by:dancebert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 13

Accepted Solution

by:
danblake earned 100 total points
ID: 10725587
Having a look here:

CREATE PROC updateEmployee3 (
     @FName varchar(10)
     , @LName varchar(10))
AS  
  select @FName,@LName

  UPDATE Employee  
  SET  fname = @Fname, lname = @Lname

SELECT @@ERROR, @@ROWCOUNT

===============================

Running the following QA (Command):
updateEmployee3 'FName','LNamefjdkfjkdfjkdjfkdjfk'

You will notice that when the vars are passed to the SP, they are truncated on input.  They will not be trapped within the @@Error value.

Input vars into SPs are automatically truncated (without error) on input this is due to no checking when passing values to variables on variable length and if exceeding input length -- generate an error.  This is different than inputing into a table.

The following demonstrates this:
DECLARE @find varchar(3)
SET @find = 'Ring'
select @find

Output
---------------------------------------------------------------
Rin
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question