Edit Question - KnowledgeHub

Title Body

According to this forum discussion , SQL Server (I'm using 2005 but I gather this also applies to 2000 and 2008) silently truncates any varchar s you specify as stored procedure parameters to the length of the varchar, even if inserting that string directly using an INSERT would actually cause an error. eg. If I create this table: CREATE TABLE testTable( [testStringField] [nvarchar](5) NOT NULL ) then when I execute the following: INSERT INTO testTable(testStringField) VALUES(N'string which is too long') I get an error: String or binary data would be truncated. The statement has been terminated. Great. Data integrity preserved, and the caller knows about it. Now let's define a stored procedure to insert that: CREATE PROCEDURE spTestTableInsert @testStringField [nvarchar](5) AS INSERT INTO testTable(testStringField) VALUES(@testStringField) GO and execute it: EXEC spTestTableInsert @testStringField = N'string which is too long' No errors, 1 row affected. A row is inserted into the table, with testStringField as 'strin'. SQL Server silently truncated the stored procedure's varchar parameter. Now, this behaviour might be convenient at times but I gather there is NO WAY to turn it off. This is extremely annoying, as I want the thing to error if I pass too long a string to the stored procedure. There seem to be 2 ways to deal with this. First, declare the stored proc's @testStringField parameter as size 6, and check whether its length is over 5. This seems like a bit of a hack and involves irritating amounts of boilerplate code. Second, just declare ALL stored procedure varchar parameters to be varch

Tags (comma-separated) Cancel