Truncate values in table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Truncate values in table

Author

Topic

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2008-06-09 : 18:07:59

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.

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!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-09 : 18:10:26

Why don't you set your parameter's data type to the same thing as the column in the table? Then in your application, pass only the characters to be inserted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2008-06-09 : 18:13:01

Hi tkizer,
I'm new to SQL server. I'll appreciate if you can show me the code sample. I'm still not clear.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-09 : 18:46:17

What I am saying is to handle this in your application and not in the stored procedure.

Also notice how you've set the parameter to 256, use 255 instead to match the column in the table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2008-06-09 : 18:49:42

Hi tkizer,
Isn't it a good practice to do the validation in Stored proc since it will be pre-compiled??

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-09 : 18:54:21

I don't see any benefit to doing the validation in the stored procedure.

If you want to do the validation in your stored procedure, then you are going to need to modify the size of the parameter to however many characters your applications allows, and then use either the LEFT or SUBSTRING functions during the INSERT to get only "n" number of characters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-06-12 : 23:50:41

Freephoneid.

Tara is right. You may use the sp as modified below

ALTER PROCEDURE [dbo].[AddSystemEvent]
(
@ScannerID As Int,
@TimeDate DateTime,
@EventID As Int,
@EventDesc NVarchar(1000) -- Modify this to the max characters allowed by the front end
)
AS
Begin
Insert Into tb_system_event
(Scanner_ID, Time_Date, Event_ID, Event_Description)
Values (@ScannerID, @TimeDate, @EventID, Substring(@EventDesc, 1, 255) -- see the use of substring function
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources