truncate value in a table

Question

Post reply

truncate value in a table

freephoneid

Newbie

Points: 2
More actions
June 9, 2008 at 4:31 pm

#187737

Hi,
I've a column Event_Desc(255)nvarchar in a table. If I insert a record in it through stored procesure which is more than 255 chars, it gives error.
Can any one give me the code in stored proc format which will check if length of event_desc entered by user not more than 255 & that if it is it'll just accept first 255 chars & insert it into database.
I'm using SQL Server 2000.
Here is my existing stored proc:
CREATE PROCEDURE [dbo].[AddSystemEvent]
(
@ScannerID As Int,
@TimeDate DateTime,
@EventID As Int,
@EventDesc NVarchar(256)
)
AS
Begin
Insert Into tb_system_event
(Scanner_ID, Time_Date, Event_ID, Event_Description)
Values (@ScannerID, @TimeDate, @EventID, @EventDesc)
End
Appreciate any help!!!!
Thanks!!!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323522 More actions · Answer 1

maybe i'm just misreading it, but you want to insert just 255 chars right?

CREATE PROCEDURE [dbo].[AddSystemEvent]

(

@ScannerID As Int,

@TimeDate DateTime,

@EventID As Int,

@EventDesc NVarchar(256)

)

AS

Begin

Insert Into tb_system_event

(Scanner_ID, Time_Date, Event_ID, Event_Description)

Values (@ScannerID, @TimeDate, @EventID, LEFT(@EventDesc,255))

End

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

freephoneid Newbie Points: 2 More actions · Answer 2

Thanks !!!

Can you also tell me how to test this stored procedure?

Raghuram (AJ) Ten Centuries Points: 1214 More actions · Answer 3

freephoneid (6/9/2008)
Hi,
I've a column Event_Desc(255)nvarchar in a table. If I insert a record in it through stored procesure which is more than 255 chars, it gives error.
Can any one give me the code in stored proc format which will check if length of event_desc entered by user not more than 255 & that if it is it'll just accept first 255 chars & insert it into database.
I'm using SQL Server 2000.
Here is my existing stored proc:
CREATE PROCEDURE [dbo].[AddSystemEvent]
(
@ScannerID As Int,
@TimeDate DateTime,
@EventID As Int,
@EventDesc NVarchar(256)
)
AS
Begin
Insert Into tb_system_event
(Scanner_ID, Time_Date, Event_ID, Event_Description)
Values (@ScannerID, @TimeDate, @EventID, @EventDesc)
End
Appreciate any help!!!!
Thanks!!!

Replace @EventDesc NVarchar(256) with @EventDesc NVarchar(255). If you limit the input to only 255 character, you should not be requiring any extra functions.

The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner

Lowell SSC Guru Points: 323522 More actions · Answer 4

from Query analyzer or SQL 2005 Management Studio:

exec AddSystemEvent 12,'06/09/2008',-21,'Some Bad Event Occured that probably should not have.'

note the scanner id example i put in an arbitrary number of 12, and did something similar with The EventId (-21)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

truncate value in a table

Cookies on SQLServerCentral