| Author |
Topic |
|
stahorse
Yak Posting Veteran
86 Posts |
 Posted - 2012-11-21 : 08:20:03
|
| HiI want to validate my input parameter, which is policy Id (int)So I have:declare @Valid_PolicyId int select @Valid_PolicyId = count(*)from Selestia_BI_Staging_Source.dbo.PR_REB_SNAPSHOT rs WITH (NOLOCK)where rs.POLICY_ID = @PolicyId--check if PolicyId inputed--if (@PolicyId is null OR @PolicyId = '') BEGIN RAISERROR ('Policy Id is required', 16, 1) RETURN (-1) END -----check for valid PolicyId-----if (@Valid_PolicyId = 0) BEGIN RAISERROR ('Incorrect Policy Id', 16, 1) RETURN (-1) END this code works fine, but I just found out that when I input long digits like '222222222222222222222' my code breaks and I get this error: Error converting data type varchar to int.Now I need to make other input valid for long input like that, which breaks from the 13th character, which gives me a problem.Please help. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-21 : 08:35:31
|
| Use BIGINT instead of int, but even big int only gets you up 2^63 -1.JimEveryday I learn something that somebody else already knew |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-21 : 08:37:16
|
Make @PolicyID an INT parameter to a stored procedure.CREATE PROCEDURE dbo.YourProcedure @PolicyID INT AS... That will ensure that someone can send only a valid integer. So you won't have to check against something like empty spaces or overflow integers. Then all you would need to do is to check if the policy is within valid range of policy id's that are allowed by other business rules. |
|
|
|
stahorse
Yak Posting Veteran
86 Posts |
Posted - 2012-11-21 : 08:38:10
|
| Yes, BIGINT takes only 19 characters. |
|
|
|
stahorse
Yak Posting Veteran
86 Posts |
Posted - 2012-11-21 : 08:40:53
|
| @sunitabeck@PolicyId it is declared as INT. |
|
|
|
stahorse
Yak Posting Veteran
86 Posts |
Posted - 2012-11-21 : 08:48:19
|
| I wanted to code it that if they input anything more than 9 digits it shud throw an error. |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-21 : 08:54:04
|
| Then check the condition like IF ( len(@policy_id) > 9) ..........Another solution isUse TRY... CATCH blocks for raising error if data conversion is failed--Chandu |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-21 : 09:02:11
|
| BIGINT doesn't take 19 characters, it holds values between -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)so 9,223,372,036,854,775,808 isn't valid even though it is 19 charactersINT holds values between 2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)so a 9-digit number > 2147483647 is still not valid int, so perhapsyou checks should be based around the actual values or your need to change yourinput data type, or both.JimEveryday I learn something that somebody else already knew |
|
|
|
stahorse
Yak Posting Veteran
86 Posts |
Posted - 2012-11-21 : 09:08:18
|
| I have tried that I'm still getting the same error: Error converting data type varchar to int. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-21 : 09:30:34
|
| So I'm assuming that the front end is going to send in whatever a user types, without checking its validity. I've worked in insurance and it's amazing what people type in as a policy number! If the front end isn't going to validate,you're going to have to change the data type of your input parameter to varchar(n), and then start validating from there, and like Bandi says, use try catch to raise errors on conversion failures.JimEveryday I learn something that somebody else already knew |
|
|
|
|
Input Validatopn