Parameter truncation doesn't raise error in stored proc
Posted on 2004-03-31
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.
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
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 (
, @LName varchar(10))
SET fname = @Fname, lname = @Lname
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.