Recently I was referring to books online to confirm one strange behavior in SQL Server. In one of the legacy applications, string was being passed to the stored procedure as parameter and it was storing only 30 characters. To my surprise when I saw the code, everything seemed OK. Later on I found out that the problem. The fault was while converting parameter to varchar there was no size defined.
As per books online,
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
Check out the below script which will return only first character of parameter being passed instead of parameter value :
CREATE PROCEDURE GetEmployeeName @Name varchar AS BEGIN SET NOCOUNT ON; SELECT @Name END GO
Now if you define the parameter length, but using cast or convert function, it will return first 30 characters.
ALTER PROCEDURE GetEmployeeName @Name varchar(40) AS BEGIN SET NOCOUNT ON; SELECT Convert(varchar,@Name) as Name END GO --The longest one-word name of Hawaiian boy exec GetEmployeeName Kananinoheaokuuhomeopuukaimanaalohilo
Make sure length is defined for all stored procedure parameters and variables to avoid unexpected behavior.
Source : Books Online