I have some sql procedure (MSSQL 2019) if relevant

Create PROCEDURE [dbo].[test] @tmp varchar(32)  AS
print @tmp

why when i do

EXEC    @return_value = [dbo].[test]
        @tmp = N'SSL99999999999999999999999999999900000006785999999999999999'

I se it use 32 chars substring in @tmp ? SSL99999999999999999999999999999 And no exception that string or binary data would be truncated as i expected ?

Can i turn on such behavouur ? i would need this if possible ;)

Thanks and regards !

1 Answer 1

Declare @i varchar(5)='ppppppppppp'
Select @i 
--- output ppppp (without any error)

Declare @t table(col varchar(5))
insert into @t values('pppppppppp')
Select * from @t

--Error message "string or binary data would be truncated"

So it is while insert or Update this error will come if column length is less. When same thing is assign to any variable then length automatically get truncated without any warning or error.This is default behaviour

  • i see thank You. i guess there is no way / some directive etc to force in some cases to throw exception on this kind of errors?
    – Dorian
    Feb 13 at 9:29
  • @Dorian,Yes there is way out,you have to write your own logic to throw error,if data length exceed.Wait I am updating my answer.
    – KumarHarsh
    Feb 13 at 10:34
  • this i known thanks ;) i was thinking about something like set auto_trunc off etc - i see oracle have something similar but guess mssql still not - strange a bit imho ;p –
    – Dorian
    Feb 13 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.