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.
| Author |
Topic |
|
jmiaebrown
Starting Member
1 Post |
 Posted - 2008-12-23 : 14:38:47
|
| Hello. I have a stored procedure that takes in two arguments. I have defined them as varchar(8). Why is the argument being trucated to 8? How can I define the procedure so that if the argument is greater than 8 than there is a failure?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2008-12-23 : 15:05:49
|
| I agree with Tara in that you want to validate the data before it enters the sql query. There is more than one reason for that. At the very least it is a layer of protection against getting invalid data into your data table.But, if you must validate it at the sql level you may wish to simply use the length function.. ie select len(name) to get the length of the field and then do something with it if its > 8.example..DECLARE @name varchar(8)set @name = select name from sometableif len(@name) > 8print 'length is greater than 8'you could also do this..select name from sometablewhere len(name) > 8regards,r&r |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2008-12-23 : 15:35:00
|
| Ok I see that now that I went back and read the initial post again.He has set a limitation on column size.You will have to validate the data before the sql proc is called.. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2008-12-23 : 15:58:49
|
quote:
Originally posted by jmiaebrown
Hello. I have a stored procedure that takes in two arguments. I have defined them as varchar(8). Why is the argument being trucated to 8?
Because thAT'S HOW YOU DEFINED IT?quote:
How can I define the procedure so that if the argument is greater than 8 than there is a failure?
Yes, make the field bigger, and check if LEN(@var) > nBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-12-23 : 17:01:33
|
| It would be simpler to have the front end developer change the application so that it does not accept or send a value that is longer than 8 characters.CODO ERGO SUM |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2008-12-23 : 17:11:18
|
| I think this is what Tara was saying earlier.The data will automatically be truncated to 8 because this is how the column was defined. You cannot check for it because it will already be defined before the stored procedure is called. It will always be 8 or less.Therefore you must check it on the apps side.r&r |
|
|
|
|
|
|
Stored Procedure Truncated