November 21, 2012 at 6:21 am
Hi
I 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, cause it breaks from 13th character, which gives me a problem.
Please help.
November 21, 2012 at 6:38 am
hoseam (11/21/2012)
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, cause it breaks from 13th character, which gives me a problem.
Please help.
You could change the PolicyId from int to bigint.
See info on the data types here: http://msdn.microsoft.com/en-us/library/ms187745.aspx
November 21, 2012 at 6:42 am
I have but bigint only takes 19 characters.
November 21, 2012 at 6:46 am
How many characters do you need it to take? (or really, its a numeric length)
It's also possible that I don't understand exactly what you're looking for. Are you trying to make sure that the length is less than a certain number of characters and if not return an error?
November 21, 2012 at 6:48 am
I don't know how many digits will tester input, But I wanted to code it that if they input anything more than 9 digits it shud throw an error.
November 21, 2012 at 6:53 am
Depending on what exactly is happening, there are a couple of ways to do it. If @PolicyId is an int like you state, the following should work
--check if PolicyId inputed--
if (@PolicyId is null OR @PolicyId = '' OR @PolicyId > 999999999)
However, it looks like you're treating @PolicyId as a string. If it is a string, you could do:
--check if PolicyId inputed--
if (@PolicyId is null OR @PolicyId = '' OR LEN(@PolicyId) > 9)
Edit: Or, maybe put the @PolicyId > 999999999 part to return its own error code
November 21, 2012 at 7:08 am
I have tried that I'm still getting the same error: Error converting data type varchar to int.
November 21, 2012 at 7:35 am
since you don't know what the user will throw at the SP, I would suggest you cahnge the data type of the input parameter to be a varchar.
Then, within the SP, you can do your tests to determine if the param is in actual fact a number (keeping in mind the issues around IsNUmeric see: http://www.sqlservercentral.com/articles/IsNumeric/71512/) and then carry on as appropriate.
I would also suggests that, if possible, you add some param checking on your front end interface.
HTH,
B
November 21, 2012 at 7:55 am
It sounds like you're having a conversion issue. Can you paste more of the top of the procedure? (Like where the procedure and the variables are declared)
November 21, 2012 at 8:53 am
It sounds like you have an appication that allows users to enter a policy, so why not validate the number that the user entered in the application to make sure its the right datatype.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 21, 2012 at 6:42 pm
Try it like this:
DECLARE @Valid_PolicyId INT
DECLARE @PolicyIdInput VARCHAR(100) = '222222222222222222222'
DECLARE @PolicyID INT
DECLARE @Err INT = 0
--check if PolicyId inputed--
IF ISNULL(@PolicyIdInput, '') = ''
BEGIN
RAISERROR ('Policy Id is required', 16, 1)
RETURN (-1)
END
BEGIN TRY
SELECT @PolicyID = CAST(@PolicyIdInput AS INT), @Err = 0
END TRY
BEGIN CATCH
SELECT @Err = 1
END CATCH
IF @Err = 1
BEGIN
RAISERROR ('Too many digits in Policy Id', 16, 1)
RETURN (-1)
END
-----check for valid PolicyId-----
if NOT EXISTS(
SELECT 1
FROM Selestia_BI_Staging_Source.dbo.PR_REB_SNAPSHOT rs WITH (NOLOCK)
WHERE rs.POLICY_ID = @PolicyId)
BEGIN
RAISERROR ('Incorrect Policy Id', 16, 1)
RETURN (-1)
END
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply