It seems that there is no error message if the length of the parameter
value exceeds parameter datalength
Create Procedure #test (@.data varchar(10))
as
Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
The result is
This is te
Why does SQL Server not raise any error?
MadhivananThat is what is called implicit conversion. Read BOL on this
Regards
R.D
--Knowledge gets doubled when shared
"Madhivanan" wrote:
> It seems that there is no error message if the length of the parameter
> value exceeds parameter datalength
> Create Procedure #test (@.data varchar(10))
> as
> Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
> The result is
> This is te
> Why does SQL Server not raise any error?
>
> Madhivanan
>|||Hi
You just "selected" the data.SQL Server cuts off the result. Try doing
insertion and you will get the error
Create Procedure #test (@.data varchar(10))
as
create table #t (col varchar(2))
insert into #t Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1129800867.933647.36380@.g49g2000cwa.googlegroups.com...
> It seems that there is no error message if the length of the parameter
> value exceeds parameter datalength
> Create Procedure #test (@.data varchar(10))
> as
> Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
> The result is
> This is te
> Why does SQL Server not raise any error?
>
> Madhivanan
>|||But when you give width 10, there is no error
Create Procedure #test (@.data varchar(10))
as
create table #t (col varchar(10))
insert into #t Select @.data
Go
Exec #test 'This is testing'
Go
Drop Procedure #test
Madhivanan|||well,because @.data is already varchar(10) (sql server cuts off) and your
column had declared as varchar(10) , what's problem?
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1129802989.571260.209840@.f14g2000cwb.googlegroups.com...
> But when you give width 10, there is no error
> Create Procedure #test (@.data varchar(10))
> as
> create table #t (col varchar(10))
> insert into #t Select @.data
> Go
> Exec #test 'This is testing'
> Go
> Drop Procedure #test
>
> Madhivanan
>|||> But when you give width 10, there is no error
That's right, because the parameter already made your data 10 characters
long, which fits just nicely in a VARCHAR(10) column.
You realize that not all data validation *has* to happen within the
database, right?|||Well
My question is why does SQL Server doesnt raise an error when the
length of value is nore than the parameter length?
I expect the same error that happend in this case
Declare @.t table(data varchar(10))
insert into @.t values('This is testing')
select data from @.t
Server: Msg 8152, Level 16, State 9, Line 2
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
Madhivanan
