Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

problem with stored procedure Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 1:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 11:10 PM
Points: 2, Visits: 6
I have a table
Create table SINHVIEN (
MASV nvarchar(20) not null,
HOTEN nvarchar(100) not null,
NGAYSINH datetime,
DIACHI nvarchar(200),
MALOP varchar(20),
TENDN nvarchar(100) not null,
MATKHAU varbinary not null,
constraint PK_SINHVIEN primary key (MASV)
)
go

and I want to create a stored procedure to insert values for this table in security by use md5 hash function.
CREATE PROCEDURE SP_INS_SINHVIEN
@MASV nvarchar(20),
@HOTEN nvarchar(100),
@NGAYSINH datetime,
@DIACHI nvarchar(200),
@MALOP varchar(20),
@TENDN nvarchar(100),
@MATKHAU varchar(100)
AS
INSERT INTO SINHVIEN
VALUES
(
@MASV,
@HOTEN,
@NGAYSINH,
@DIACHI,
@MALOP,
@TENDN,
HASHBYTES('MD5',@MATKHAU)
)
GO

but when I try to execute this stored procedure,

insert into SINHVIEN values ( '1012283','Vo Thi Yen Nhi', '01/01/1992',  'VietNam', 'TH2010/4', 'meomun', HashBytes('MD5', '123456'))

I get error

Msg 8152, Level 16, State 11, Line 1
String or binary data would be truncated.
The statement has been terminated.

I debug and know the problem is HashBytes('MD5', '123456')
how can I do to fix this problem?

Post #1446820
Posted Friday, April 26, 2013 2:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:24 PM
Points: 3,098, Visits: 3,231
Hi,

Change table:

alter table SINHVIEN
alter column MATKHAU varbinary(max) not null

Change SP:

Alter PROCEDURE SP_INS_SINHVIEN
@MASV nvarchar(20),
@HOTEN nvarchar(100),
@NGAYSINH datetime,
@DIACHI nvarchar(200),
@MALOP varchar(20),
@TENDN nvarchar(100),
@MATKHAU varchar(8000)
AS
INSERT INTO SINHVIEN
VALUES
(
@MASV,
@HOTEN,
@NGAYSINH,
@DIACHI,
@MALOP,
@TENDN,
convert(varbinary(max),HASHBYTES('MD5',@MATKHAU))
)
GO

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1446845
Posted Friday, April 26, 2013 3:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 11:10 PM
Points: 2, Visits: 6
Thank you so much!
Send from VietNam!
Post #1446867
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse