Confused

[saguni]

Hi friends,

I am having a doubt in insertion of data

create table table1(name varchar(10))

In this table, when i insert the following record manually, it produces me an error that it cannot insert.

insert into table1 values('asdfghjklqwe')

But when I try to insert it with the procedure, it trims the data upto 10 digits the data shown is

"asdfghjklq"

How can this be possible?

Feeling really confused...

Can anyone help me ????

[pritaeas]

You defined a varchar 10, so you cannot insert more than 10 characters. It will throw an error. (Unlike MySQL which will just trim the data.)

[saguni]

hi @pritaeas,
I got the point but about the same when inserted by a procedure
???

That the real confusion here...

[pritaeas]

What procedure are you talking about?

[saguni]

Ok.. I will explain it breifly...

I am using a asp.net frontend.When save button is clicked I call a SQL-Procedure to get executed. this procedure contains the insert query.

------------------------------------------------------------------------------

My Problem is:

Scenario 1:

When I insert the data displayed above in sql server application, Its showing me a error that it cannot insert.
Scenario 2:
When I pass it throgh a procedure, it trims by default w/o showing any error.

I am in scenario 2...

Why is this kind of behaviour with MSSQL?

[pritaeas]

Probably because the parameter you pass to the procedure is trimmed.

[saguni]

but the parameter is declared as varchar(100)...

Are you still sure about your answer?

[pritaeas]

It would've been helpful if you mentioned that at the start. I can't be sure because I cannot test this atm. What version are you using?

If you want to be absolutely sure I suggest you ask this on MSDN.

[saguni]

I am using

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )

and

Microsoft Visual Studio 2010 version 10.0.40219

Anyway I will also post this question on MSDN.

Thanks for your reply @pritaeas

[ugi.nagesh]

Hi,
See the sample that i have attached to confirm is this

i am gatting the error for both direct insert and stored procedure...

create table table1(name varchar(10))
go

Case 1 :
insert into table1 values('12345678901') -- more than 10 characters

Msg 8152, Level 16, State 14, Procedure test, Line 6
String or binary data would be truncated.
The statement has been terminated.


Case 2 :

alter procedure test
@par varchar(100)
as
begin
 insert into table1 values(@par)
end
go

exec test '12345678903333' -- more than 10 characters
go

Msg 8152, Level 16, State 14, Procedure test, Line 6
String or binary data would be truncated.
The statement has been terminated.


Case 3:

create procedure test1
@par varchar(10)
as
begin
 insert into table1 values(@par)
end
go

exec test1 '1234567890144' -- it will truncate in the sp parameter "@par" to 10 characters
go

-- will execute without any issues

If you are passing from .net code , then while executing the stored procedure it will truncate the value to "10" and insert into the table. Extra characters will be truncated.

please correct if i am missing something..